Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Neal369
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.

 

Neal369_0-1680318474322.png

 

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
Walter_W2022
Resolver II
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
 

wangbt89_0-1680338016135.png

 

View solution in original post

1 REPLY 1
Walter_W2022
Resolver II
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
 

wangbt89_0-1680338016135.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors