Forum Discussion

Neal369's avatar
Neal369
New Member
3 years ago
Solved

Difference between measures - Based on Date Values

Hi Friends,

 

I am having below excel data, 

How can we calculate difference between total sales values with regrads to current date and previous date in table.

 

 

I have calculated, Last Date as,

 

LastDate = CALCULATE(LASTDATE(Table1[ReportingDate]), FILTER(ALLSELECTED(Table1), Table1[ReportingDate] < MAX(Table1[ReportingDate])))
 
However, I am facing challange while calculating Total Sales corresponding to last date in order to calculate difference.
  • Walter_W2022's avatar
    Walter_W2022
    3 years ago

     

    Neal369 , the dates are not consecutive date, so you have to add index for the date table which extract from the fact table, so we can get the previous date sum, please see below screen shot, and also attached pbix file. the main measure is as below

    _variance_preious_date =
    VAR _lastDate = SELECTEDVALUE(dimDate[Index])-1
    VAR _sumCurrent = CALCULATE(sum('fact'[Sales]))
    VAR _sumLastDate = CALCULATE(sum('fact'[Sales]),dimDate[Index]=_lastDate,REMOVEFILTERS(dimDate))
    VAR _value = IF(_lastDate = 0, _sumCurrent, _sumCurrent-_sumLastDate)
    RETURN
    _value
     

     

1 Reply

  • Walter_W2022's avatar
    Walter_W2022
    Resolver II
    3 years ago

     

    Neal369 , the dates are not consecutive date, so you have to add index for the date table which extract from the fact table, so we can get the previous date sum, please see below screen shot, and also attached pbix file. the main measure is as below

    _variance_preious_date =
    VAR _lastDate = SELECTEDVALUE(dimDate[Index])-1
    VAR _sumCurrent = CALCULATE(sum('fact'[Sales]))
    VAR _sumLastDate = CALCULATE(sum('fact'[Sales]),dimDate[Index]=_lastDate,REMOVEFILTERS(dimDate))
    VAR _value = IF(_lastDate = 0, _sumCurrent, _sumCurrent-_sumLastDate)
    RETURN
    _value