Calculate Actual from Cumulative

[Mat42]

This might be a stupid question, but I've searched and found nothing helpful. Also, this post is long winded, but I wanted to ensure I gave all the necessary information.

We have about 80 indicators that track quarterly data from various department in our organisation. Each quarter a department will submit their data for that period. The problem is that departments have historically been given a lot of leeway with regards to how they submit this data, meaning that some do it one way, and some do it another. We've tried to make the process more uniform, but there has been a lot of pushback, so I thought I'd try and find a different solution.

The crux of the problem is that some departments submit a quarterly figure, that we then use to calculate a running cumulative for the year, whilst others submit the cumulative figure and have us calculate their actual.

So, for instance, Department 1 might submit the following figures:

Q1 - 10

Q2 - 5

Q3 - 12

Q4 - 3

And we'll produce a cumulative figure:

Q1 - 10

Q2 - 15

Q3 - 27

Q4 - 30

Whilst Department 2 submits their data as a cumulative, with us calculating the actual. So they'd essentially submit the second set of data above (10, 15, 27, 30), and we'd calculate the first set of data (10, 5, 12, 3).

The problem is, I can't work out a simple way to calculate things for Department 2. I have a thoroughly clunky setup where I order the data correctly, add 2 Index columns (with the first starting at 0 and the second starting at 1), then merge the table with itself, using the index columns as merge points so the the current quarters data and previous quarters data are on the same row then add in a calculated column the deletes one from the other if it's a Cumulative Actual indicator (I have a column labelled 'Code' that identifies with 'CA' whether the indicator is a cumulative actual).

But there must be a better, less ham-fisted way to do it.

An very sanitised, heavily edited example of the data is below:

Is anyone able to give me a better way to calculate the yellow column?

[amitchandak · Accepted solution]

Create a new column Qtr No and then one of the two result calculations should work for you. The result is also a new column

Qtr No = right(QUARTER,1)

result = table[Actual]- maxx(filter(table,table[Indicator no]=earlier(table[Indicator no]) && table[code]=earlier(table[code])
				&& table[Qtr No] =(earlier(table[Qtr No])-1)),table[Actual])
//OR				
result = table[Actual] -maxx(filter(table,table[Indicator no]=earlier(table[Indicator no]) && table[code]=earlier(table[code])
				&& table[Qtr No]-1 =(earlier(table[Qtr No]))),table[Actual]	)	

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Mat42]

That worked brilliantly, thanks.

Really sorry for the stupidly late reply, amitchandak, I spent so long trying to get your dax to work (it worked fine, it was my idiocy that was the problem) that I just completely forgot to come back and thank you.

I really appreciate the help, and would not have come up with that solution on my own.