Difference between Latest Date and 2nd Latest Date

[AmrZahran]

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:

Material	Requirements	Production	Date
A	50	40	16.08.2020
A	40	30	23.08.2020
B	30	20	16.08.2020
B	20	10	23.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 🙂

[Anonymous]

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

[Greg_Deckler]

@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]

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]

@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]

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]

@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]

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]

@Anonymous - Oh, yeah, yeah, yeah, I see you calculating the sum of the Requirements on the dates, yep, I was reading that wrong. So I think the only issue is that I believe your code gets the same date when calculating those sums.