Forum Discussion
Calculating Daily values from Cumulative Total
Your example starts here:
I'll add a new index column [Index1] = [Index] + 1 and then self merge:
Expand Total_Values
Define a new [Daily_Values] column as the difference between the total and the previous total.
Result:
File attached.
Hi,
This calculated column formula works
Daily value = [Total_Values]-lookupvalue(RunningTotal[Total_Values],RunningTotal[Date],CALCULATE(max(RunningTotal[Date]),FILTER(RunningTotal,RunningTotal[Date]<EARLIER(RunningTotal[Date]))))Hope this helps.
Hello zawlh
The image and DAX code are given below for your quick reference.
DailyValue2 = [DailyValue] - CALCULATE(MAX(RunningTotal[Total_Values]), FILTER(ALL(RunningTotal[Date]), RunningTotal[Date] < MAX(RunningTotal[Date])))Url to the pbix file https://drive.google.com/file/d/1pKkRWxJI56oKT2MeLsPKfF-tldwPlMzK/view?usp=sharing
Regards
Kumail Raza
LinkedIn: https://www.linkedin.com/in/kumail-raza-76508856/
If this answers your query, mark it as the solution.
Kudos are appreciated.
Hi zawlh ,
According to your description, here’s my solution.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Although I checked the above links and tried, I don't quite understand how to check those solutions. So that, I attached the file to get your help.
In the file, you may find two columns: Date, Total_Values. I would like to request to add new column to get daily update Number.
Thank you for you help.
Your example starts here:
I'll add a new index column [Index1] = [Index] + 1 and then self merge:
Expand Total_Values
Define a new [Daily_Values] column as the difference between the total and the previous total.
Result:
File attached.
It is also a good idea. Thank you for your help.
Hi,
This calculated column formula works
Daily value = [Total_Values]-lookupvalue(RunningTotal[Total_Values],RunningTotal[Date],CALCULATE(max(RunningTotal[Date]),FILTER(RunningTotal,RunningTotal[Date]<EARLIER(RunningTotal[Date]))))Hope this helps.
This helps me a lot. Thank you for your help.
You are welcome.
Hello zawlh
The image and DAX code are given below for your quick reference.
DailyValue2 = [DailyValue] - CALCULATE(MAX(RunningTotal[Total_Values]), FILTER(ALL(RunningTotal[Date]), RunningTotal[Date] < MAX(RunningTotal[Date])))Url to the pbix file https://drive.google.com/file/d/1pKkRWxJI56oKT2MeLsPKfF-tldwPlMzK/view?usp=sharing
Regards
Kumail Raza
LinkedIn: https://www.linkedin.com/in/kumail-raza-76508856/
If this answers your query, mark it as the solution.
Kudos are appreciated.