take the different between top 2 days

[Anonymous]

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

[Anonymous]

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)

[Anonymous]

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

Thank you.

[Fowmy]

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

)

[Anonymous]

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.

[Fowmy]

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

[Anonymous]

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.

[Fowmy · Accepted solution]

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

[Anonymous]

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.

[Fowmy]

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

[Anonymous]

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