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/filtering-and-comparing-different-time-periods-with-power-bi/

 

I'm trying to achive something like this:

 

my raw data looks something like this from(date from 2018 to 2020):

for example if my:

first range of dates was from 1/2/2018 to 1/5/2018

second range of dates from 5/8/2020 to 5/10/2020

 

Then, compare value from the prior set of data ( finding the max value) to the more current set of data finding the max value.  I need to also show which date that Max day was.

 

 

I will get:

123 for 'g' on date 1/2/2018  vs 67678 for 'g' on 5/10/2020.  (67678/123-1 to get YOY)

32425 for 'ng' on date 1/3/2018 vs 356 for 'ng' on 5/9/2020  (356/32425-1)

3456 for 'ing' on date 1/4/2018 vs 3456 for 'ing' on 5/8/2020 (3456/3456-1)

 

 

I hope i've made this clear....

 

 

Thanks all!

 

Mike

 

 

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.

7 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi AllisonKennedy ,

       

      Thank you so much for your time.

      The values are fine.

       

      However for some reason I am only getting the last date of the filters:

       

      I have plugged in my data into your pbix and I noticed that that the only diff was that my table's cal was actual calendar dates.

      Why would this cause it to only show the last date?

       

      this is my actual data (over two years of data):

       

      Thank you,

      Mike

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        6 years ago
        Anonymous

        Sorry, I created the variable and then chose the wrong value. I will edit my original post now so as not to confuse any future users that find this for reference, but please use this measure for date:

        Current Date =
        VAR MaxValue = [Current Max]
        RETURN
        MAXX(FILTER('Table', 'Table'[Value] = MaxValue), 'Table'[Date])

        Note the difference here is that we use the MaxValue VARIABLE rather than the [CurrentMax] MEASURE in our return value, and this provides the correct result.
        Variables are calculated when they are defined, whilst measures are calculated when they are used. So using the VARIABLE for the comparison in the MAXX(FILTER('Table', 'Table'[Value] = MaxValue), 'Table'[Date]) part of the formula ensures that we are using the already defined value for MaxValue. Using the Measure makes that too dynamic and essentially allows the MAXX to see every row in the table. For each row, the max value of that row is itself.

        I think I'm just rambling and not making much sense, but the above measure should work better for you and hopefully you get some understanding why.
  • 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

       

       

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      4 years ago

      hello everyone, something similar happens to me in PBI, I have the minimum and maximum value of the multimplos, but I need the minimum date of the minimum multiplo, not the minimum date of the fehcas, attached image to make it look better

      joselin_0-1648744458721.pngjoselin_1-1648744475296.png