+ Reply to Thread
Results 1 to 7 of 7

DAX/Measures MAX with Dynamic Filtering

  1. #1
    Registered User
    Join Date
    11-06-2017
    Location
    Lincoln, Nebraska
    MS-Off Ver
    Office 2016
    Posts
    6

    DAX/Measures MAX with Dynamic Filtering

    Hello! I'm new to PowerPivot and don't know all the terminology yet, sorry if this has been answered before.

    I created a sample table where I have the Movie Title, Movie Studio, Type, Reporting Period, Quantity Sold, and Date of Report. There are two Types, DVD and BlueRay. The sales for these Types come in on two different reports. There are also multiple amended sales (either due to additional sales or returns). As a result, I need to filter for the MAX Date of Report for both DVD and BlueRay, SUM the sales of DVDs and BlueRays based on the respective MAX Date of Report and they have a slicer or filter for the Reporting Period (month).

    The problem I'm running into is the MAX Date of Report needs to be based on Reporting Period (selected on slicer) and Type, so only the MAX Date of Report for all BlueRays is returned for March, not the MAX Date for March for BlueRays for each Movie Studio.

    For the measures I have

    Max Date of Report - BlueRay =CALCULATE(MAX([Date of Report]),Sales[Type]="BlueRay")
    Max Date of Report - DVD =CALCULATE(MAX([Date of Report]),Sales[Type]="DVD")

    =CALCULATE(SUM(Sales[Quantity Sold]),Sales[Type]="BlueRay",FILTER(Sales,[Date of Report]=[Max Date of Report - BlueRay]))
    =CALCULATE(SUM(Sales[Quantity Sold]),Sales[Type]="DVD",FILTER(Sales,[Date of Report]=[Max Date of Report - DVD]))

    I have attached my table and screenshots.

    Thanks in advanced!

    Table.JPG
    PowerPivot.JPG
    Attached Files Attached Files

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: DAX/Measures MAX with Dynamic Filtering

    Quick clarification, before I rewrite your measures:

    Capture.PNG

    In this case, where you have more than one row with the same Movie Title, Movie Studio, Type, Reporting Period and Date of Report - what should the Sum be? 14, 19, or 33? And if 14 or 19, how are you determining?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: DAX/Measures MAX with Dynamic Filtering

    Hi

    is this way correct ?

    Max Date of Report - BlueRay:=CALCULATE(MAX([Date of Report]),Sales[Type]="BlueRay",ALL(Sales[Movie Studio]))

  4. #4
    Registered User
    Join Date
    11-06-2017
    Location
    Lincoln, Nebraska
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: DAX/Measures MAX with Dynamic Filtering

    Olly - In your case the sum should be 33. I'm trying to get it to take the latest (MAX) Date of Report for the month based on Type and ignore Movie Title and Movie Studio. When I manually filter I'm filtering for Type and then Date of report. In the below example I see the latest BlueRay Date of Report is 6/23/19 so I would add all the BlueRay sales for that report.

    Dax Question 2.JPG
    Last edited by lanmel; 07-25-2019 at 09:54 AM.

  5. #5
    Registered User
    Join Date
    11-06-2017
    Location
    Lincoln, Nebraska
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: DAX/Measures MAX with Dynamic Filtering

    Sadath31 - Unfortunately no. When I do that it fills in the same date for all the studios, but doesn't actually force the calculation on the latest date. I do really appreciate everybody's help.

    Dax Question 4.JPG

  6. #6
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: DAX/Measures MAX with Dynamic Filtering

    Okay. Try these two measures:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Put Type in the Columns section of your pivot table.

    Does that give the answers you expected?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-06-2017
    Location
    Lincoln, Nebraska
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: DAX/Measures MAX with Dynamic Filtering

    Olly - That does work. Thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using Cube function to add two measures together
    By gerardmac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2014, 04:19 AM
  2. Create Average within measures
    By NewDJ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-10-2014, 07:37 AM
  3. Worksheet Security Measures
    By smlaff01 in forum Excel General
    Replies: 0
    Last Post: 08-13-2007, 12:49 PM
  4. Calculating % of different measures taken
    By Goran in forum Excel General
    Replies: 7
    Last Post: 04-09-2006, 07:20 AM
  5. desperate measures
    By cjjoo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-19-2005, 02:38 AM
  6. Grouping measures
    By fmilano in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-04-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1