topic Re: take the different between top 2 days in DAX Commands and Tips

take the different between top 2 days

Anonymous — Sun, 31 Oct 2021 10:07:27 GMT

Hi Guys,

Could you please help me to resolve this query in DAX.

As per below image I want to take the difference between top 2 dates for each pegging key as a measure. Answer should changed based on filters.

Please help!

Power BI File attched here
https://drive.google.com/file/d/1PThqaYQGn0RvsOdNKwPsVYoC0eYuAJmW/view?usp=sharing

Re: take the different between top 2 days

Anonymous — Sun, 31 Oct 2021 10:12:49 GMT

Should be like this, Also better not to iterate over whole table and just iterate over only required columns.

Thank you.

Re: take the different between top 2 days

Anonymous — Sun, 31 Oct 2021 10:48:25 GMT

This is my Current Calc

date gap = var topdate_1 = CALCULATE( MAX('data'[CTP Date]), ALLEXCEPT('data','data'[PeggingKey]) ) var Rank_answer = RANKX( ALL('data'[PeggingKey]), MAX('data'[CTP Date]),, DESC ) var Topdate_3 = CALCULATE( MAX('data'[CTP Date]), FILTER( 'data', Rank_answer = 2 ) ) return DATEDIFF(topdate_1,Topdate_3,DAY)

Re: take the different between top 2 days

Fowmy — Sun, 31 Oct 2021 11:13:44 GMT

Anonymous

You can use this meaure:

Top 2 Diff = VAR __DATES = TOPN( 2 , VALUES(Data[CTP Date]) , Data[CTP Date]) RETURN IF( HASONEVALUE(Data[PeggingKey]), DATEDIFF( MINX( __DATES, Data[CTP Date] ), MAXX( __DATES , Data[CTP Date] ), DAY ) )

Re: take the different between top 2 days

Anonymous — Sun, 31 Oct 2021 11:24:15 GMT

Thanks this working with just Pegging key. When I bring CTP column to this table results getting wrong. Could you please fix that as well.

Re: take the different between top 2 days

Fowmy — Sun, 31 Oct 2021 11:53:19 GMT

Anonymous

Not sure why you need to bring in the CTP date here. So, how do you want the difference to be show ? only on the maximum date per Pegging Key and others blank ?

Re: take the different between top 2 days

Anonymous — Sun, 31 Oct 2021 12:16:00 GMT

Yes, Because this is part of full table. Each pegging key have multiple CTP dates. So I want to see the datediff for top 2 CTP dates next to CTP date and other can be blank or zero as well.

Re: take the different between top 2 days

Fowmy — Sun, 31 Oct 2021 12:23:12 GMT

Anonymous

Please check now: You can remove the last zero and replace it with blank if you do not want to see the other dates.

Top 2 Diff = VAR __DATES = TOPN( 2 , CALCULATETABLE( VALUES(Data[CTP Date]) , ALLEXCEPT(Data,Data[PeggingKey] )) , Data[CTP Date]) VAR __MIN = MINX( __DATES, Data[CTP Date] ) VAR __MAX = MAXX( __DATES, Data[CTP Date] ) VAR __CURRENTDATE = MAX(Data[CTP Date]) RETURN IF( NOT(HASONEVALUE(Data[PeggingKey])) , BLANK(), IF ( __CURRENTDATE IN {__MAX , __MIN}, DATEDIFF( __MIN , __MAX, DAY ), 0 ) )

Re: take the different between top 2 days

Anonymous — Sun, 31 Oct 2021 13:00:11 GMT

Thank you very much Fowmy. This working so far. Because I have sent just sample data to you. But when I add more feilds like CTP date the results dissapear. Any suggestion to fix that.

Thank you.

Re: take the different between top 2 days

Fowmy — Sun, 31 Oct 2021 13:23:21 GMT

Anonymous

How did you send the file? I am not sure about the issue you are facing, can you save it in one drive or google drive share the link here? clearly explain where you are not getting the result correct.

Re: take the different between top 2 days

Anonymous — Tue, 02 Nov 2021 05:28:50 GMT

The data set is so big I can't share that data. So I will accpet this as the solution. Thanks for the support.