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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AU555
Helper I
Helper I

Substracting values in same columns based on date column

Hi everybody, I hope I could get a little help here.2019-10-25 10_36_53-Book1.xlsx - Excel.png

I need a help to create following calculated column and that is "compared" in my sample table. We generate new dates for actual and planned finish once a month - hence the reporting date. For each reporting month, the dates for actual and planned finish might differ, as is shown in the table. In my date diff column I am calculating actual finish - planned finish. The "date difference previous reporting month" column is the same, just accounting for dates recorded previosu reporting month - actual finish -1 and planned finish -1. Now my question comes: I need to compare those two date differences to show if we have worsen in following the deadlines or on the other hand if we improved. therefore I need to substract "date diff" minus "date difference previous reporting month". What I did I thought is correct was to merge these two differences in one columns, as I thought it would be easier to substract them like this. But I have been only capable of making calculation to substract the smallest value from the biggest value:
Change days column =
var min_value = CALCULATE(MIN(table[merged date difference]); ALLEXCEPT(table; table[product])
var max_value = CALCULATE(MAX(table[merged date difference]); ALLEXCEPT(table; table[product])
return
min_value - max_value

But it is not always the case that "date diff" is the smalles, as for example in the last product. I always need to substract date diff - datw difference previous reporting month.
Can comebody help me, I got a bit stuck.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@AU555 

Column =
VAR PrevDate = CALCULATE(MAX(Test[ReportingDate]),FILTER(ALL(Test[ReportingDate]),Test[ReportingDate]<MAX(Test[ReportingDate])),ALLEXCEPT(Test,Test[Product]))
VAR PrevValue = CALCULATE(SUM(Test[PrevDateDiff]),Test[ReportingDate]=PrevDate,ALLEXCEPT(Test,Test[Product]))
RETURN CALCULATE(SUM(Test[DateDiff])-PrevValue,ALLEXCEPT(Test,Test[Product]))

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@AU555 

Column =
VAR PrevDate = CALCULATE(MAX(Test[ReportingDate]),FILTER(ALL(Test[ReportingDate]),Test[ReportingDate]<MAX(Test[ReportingDate])),ALLEXCEPT(Test,Test[Product]))
VAR PrevValue = CALCULATE(SUM(Test[PrevDateDiff]),Test[ReportingDate]=PrevDate,ALLEXCEPT(Test,Test[Product]))
RETURN CALCULATE(SUM(Test[DateDiff])-PrevValue,ALLEXCEPT(Test,Test[Product]))

Than you so much, works just as i wanted to! 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors