Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

updated title: Using 2 slicers, comparing only the max value from two range of dates & when max was

Hi,   UPDATE:  I had to updae this post cuz my orginal was confusing.... Hope I've made it clearer....   Need help (again).....   I am using this slicer setup: https://www.sqlbi.com/articles/f...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago

    Anonymous 

     

    @mikelee1701
    Not sure this will work with the YEAR listed in your Excel desired result screenshot, but following the same pattern as the formula in the blog link you posted, you can try to create this for max previous value:

    MaxPrevious=
    CALCULATE (
    MAX(table[value]),
    ALL ( 'Date' ),
    USERELATIONSHIP ( 'Date'[Date], 'Previous Date'[Date] )
    )

     

    And this for the Max previous date: 

    Previous Date = CALCULATE([Current Date], ALL('Date'), USERELATIONSHIP( 'Date'[Date], PreviousDate[Date]))
     
    Where this is the 'Current' date (you can give it a better name, but basically using the main date selection filter):
    Current Date =
    VAR MaxValue = [Current Max]
    RETURN
    MAXX(FILTER('Table', 'Table'[Value] = MaxValue), 'Table'[Date])
    *UPDATE: Note this current date measure is different to what is in the attached report - the attached report is INCORRECT. Please use the above syntax instead. 
    Current Max = MAX('Table'[Value])
     
    And YoY is just the difference divided: 
     
    YoY = DIVIDE([Current Max]-[Previous Max], [Previous Max])
     
    The challenge is in formatting the visual, so are you flexible with how it looks? See attached example; below signature.
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - I'm not following this entirely. Maybe Lookup Min/Max? https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

 

If not, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks Greg,

Your suggestions all over the forums have helped me with other problems I've had in the past.

 

Basically, I am using a slicer ( to filter) a range of dates, (call it previous period), and a range of dates ( call it current period).   I want to compare ONLY the single MAX value from previous dates to the single Max value from the current range of dates.

On top of that, I want to know which date the max value occured on for both the previous period and the current period.

 

Regards,

Mike