Forum Discussion
Calculating Daily values from Cumulative Total
I have a data table with Date and Total Values for each date. But these values is not for each update. These values are total value containing previous values: like for data 3, the value is for Day1+Day2+Day3.
Since the Total_Values (second column) is already in RunningTotal, is there any way to get the daily data for the respective date.
I would like to request how to add a new column containing daily data.
Thank you.
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.
13 Replies
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.
This helps me a lot. Thank you for your help.
I've answered similar questions before:
https://community.powerbi.com/t5/Power-Query/Optimize-performance-at-Un-Cumulate-calculation/m-p/2194983See if those help. If not, then please provide sample data in a form that doesn't require typing data from a screenshot in manually.
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.
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.
I requested your shared file. I tried with your equation and I doesn't work well.
Whatever, I really appreciate your help. Thank you.
