From Cumulate to unit value

Anything related to PowerPivot and DAX Formuale
Posts: 1
Joined: Sun Apr 04, 2021 3:29 am

From Cumulate to unit value

Postby pappo » Sun Apr 04, 2021 3:39 am

Hello everyone,
I have a Excel DB similar to this table under proposal:

Date Quantity Region
(dd/mm/yyyy) (Cum. value)
01/01/2021 10 North
02/01/2021 15 North
02/01/2021 20 South
03/01/2021 25 North
03/01/2021 22 South
04/01/2021 35 North
04/01/2021 45 South

The Quantity is a measure expressed by Cumulative valute and not a unit value.
At 04/01/2021 North had in stock 35 quantity and South 45, but in 03/01 North has make 10 quantity (25-15) and South had make 2 quantity (22-20).
So, I want create a DAX Formula for Excel Power Pivot for extract the single day unit value.
The field “Region” as no importance in the formula because will be used after with the Filter of Pivot Table.
Someone has idea for create a Formula that run correctly the passage from Cumulative value to single value?
I know that the simple solution is make a Pivot Table with in the rows field the date and quantity and in the filter the Region, but The DB is too big, and there are also other table connect in the Power Pivot.

Thanks for your help and reply.


Posts: 1158
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: From Cumulate to unit value

Postby MattAllington » Sun Apr 04, 2021 8:49 am

To solve this, you need a lifetime to date time intelligence function. You should get a calendar table first, and use that in your pivot table. I don’t think it is mandatory in this case, but it is good practice.

Then you can write a measure something like this

Total Amt = var thisDate = max(table[date])
Var LastDate = calculate(max(table[date]),filter(all(table[date]),table[date]< thisDate))
Var thisQty = sum(table[qty])
Var lastQty = calculate(sum(table[qty]),table[date]=LastDate)
Return thisQty - lastQty

I haven’t tested this, but hopefully you can see the concept and work it out from there.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests