Difference between measures - Based on Date Values

[Neal369]

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 · Accepted solution]

@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

20230401-1.pbix