Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

take the different between top 2 days

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

 

 

 

  • Fowmy's avatar
    Fowmy
    4 years ago

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

      

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

    Thank you.

     

     

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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)