cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## 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.
1 ACCEPTED SOLUTION
Resolver II

@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

Resolver II

@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