Forum Discussion

markefrody's avatar
markefrody
Post Patron
3 years ago
Solved

Value Difference Between 2 Consecutive Dates

Hi everybody,   I'm trying via DAX to get the difference between 2 consecutive dates but I'm having a difficult time in creating it. Weekend (Saturday and Sunday) should be excluded in the calculat...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    2 years ago

    Hi , markefrody 

    Thanks for your sample pbix file first! Here are the steps for your need.

    We can create a measure like this:

     

    Measure = var _t = SUMMARIZE(ALLSELECTED('Data3') ,'Data3'[Plan Date MM/DD/YYYY],'Data3'[Group],Data3[Weekday],"Count",SUM('Data3'[Count]))
    var _cur_group = MAX('Data3'[Group])
    var _t2= FILTER(_t , [Group] = _cur_group)
    var _cur_date = MAX('Data3'[Plan Date MM/DD/YYYY])
    var _t3 = FILTER(_t2 , [Plan Date MM/DD/YYYY]<_cur_date && NOT( [Weekday] in {6,7}))
    var _pre_date = MAXX(_t3 , [Plan Date MM/DD/YYYY])
    return
    IF(MAX('Data3'[Weekday]) in {6,7} , BLANK() , SUM('Data3'[Count]) - SUMX(FILTER(_t3 , [Plan Date MM/DD/YYYY] = _pre_date) , [Count]))
     
    Then we can put this measure on your visual , and we can get the result as follows:

     

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
2 years ago

Hi , markefrody 

Thanks for your sample pbix file first! Here are the steps for your need.

We can create a measure like this:

 

Measure = var _t = SUMMARIZE(ALLSELECTED('Data3') ,'Data3'[Plan Date MM/DD/YYYY],'Data3'[Group],Data3[Weekday],"Count",SUM('Data3'[Count]))
var _cur_group = MAX('Data3'[Group])
var _t2= FILTER(_t , [Group] = _cur_group)
var _cur_date = MAX('Data3'[Plan Date MM/DD/YYYY])
var _t3 = FILTER(_t2 , [Plan Date MM/DD/YYYY]<_cur_date && NOT( [Weekday] in {6,7}))
var _pre_date = MAXX(_t3 , [Plan Date MM/DD/YYYY])
return
IF(MAX('Data3'[Weekday]) in {6,7} , BLANK() , SUM('Data3'[Count]) - SUMX(FILTER(_t3 , [Plan Date MM/DD/YYYY] = _pre_date) , [Count]))
 
Then we can put this measure on your visual , and we can get the result as follows:

 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

markefrody's avatar
markefrody
Post Patron
2 years ago

Hi Aniya. Your DAX is awesome! I finally got the results I wanted. Thank you very much! Really appreciate your help.