Forum Discussion

AmrZahran's avatar
AmrZahran
New Member
6 years ago

Difference between Latest Date and 2nd Latest Date

Hello everyone,

I am trying to calculate the difference between the data from the latest date and data from the 2nd latest date.

 

Here is how my data looks like:

 

MaterialRequirementsProductionDate
A504016.08.2020
A403023.08.2020
B302016.08.2020
B201023.08.2020

 

Can anyone please guide me on how to achieve that? I want to have a matrix showing for every product what was the difference in requirements between the latest date and 2nd latest date. Product A =(40-50), Product B =(20-30)

Any help would be appreciated, thanks a lot 🙂

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI AmrZahran 

     

    VAR x =
ADDCOLUMNS(
    SUMMARIZE(
        FILTER(
            ALLSELECTED('Table') ,
             'Table'[Date]=MAX('Table'[Date]) 
            ) ,
        'Table'[Date] ,
        "Latest" , 
            CALCULATE(
                SUM('Table'[REQUIREMENTS]),
                FILTER(
                    ALLSELECTED('Table'), 
                    'Table'[Date]=MAX('Table'[Date])
                    ) 
                ),
        "2nd Latest" , 
                    CALCULATE(
                SUM('Table'[REQUIREMENTS]),
                FILTER(
                    ALLSELECTED('Table'), 
                    'Table'[Date]=MAX('Table'[Date])
                    ) 
                )
            ),"pcnt_info" , [Latest]-[2nd Latest])
RETURN X
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous - I might be reading your code incorrectly but it seems like you would end up with the same date for Latest and 2nd Latest and that you would always end up with zero because you are calculating the number of days between those two and not the difference in the Requirements at those two dates. I could be reading things incorrectly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Greg_Deckler 

        You are right. My code needs a tweak. My thought was to calculate the sum of both dates and substract in the same formula.

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

    AmrZahran - This is a variation of Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

     

    Measure = 
  VAR __LatestDate = MAX('Table'[Date])
  VAR __2ndLatestDate = MAXX(FILTER('Table',[Date] <> __LatestDate),[Date])
  VAR __LatestRequirements = SUMX(FILTER('Table',[Date] = __LatestDate),[Requirements])
  VAR __2ndLatestRequirements = SUMX(FILTER('Table',[Date] = __2ndLatestDate),[Requirements])
RETURN
  __LatestRequirements - __2ndLatestRequirements
    • AmrZahran's avatar
      AmrZahran
      New Member
      6 years ago

      Thanks a lot Greg_Deckler for your response. Unfortunately there was something that I miss-typed in the description. I want to calculate the difference in requirements per product (as shown in the updated thread).

      Would that still work? Also I have some concerns as what if there is product C which exists only in one of the 2 dates? Would the missing value be assumed as ZERO and the difference still calculated? 

      Thank you

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

        AmrZahran - Yes, it should work by Product, that's just filter context that is added when doing the calculation so yes, it should work. If there is a product C that only exists for one date, that will be an issue. However there are ways around that. For example, you can do a check if something is blank and do something else. Not sure what you want in that particular instance.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi AmrZahran 

    Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou