cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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.

4 ACCEPTED SOLUTIONS
Super User

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Prodigy

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])))``````

Regards

Kumail Raza

Kudos are appreciated.

Community Support

Hi @zawlh ,

According to your description, heres my solution.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

13 REPLIES 13
Community Support

Hi @zawlh ,

According to your description, heres my solution.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

This helps me a lot. Thank you for your help.

Helper I

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.

Post Prodigy

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])))``````

Regards

Kumail Raza

Kudos are appreciated.

Helper I

Helper I

I requested your shared file. I tried with your equation and I doesn't work well.
Whatever, I really appreciate your help. Thank you.

Post Prodigy

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

This helps me a lot. Thank you for your help.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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.

Helper I

It is also a good idea. Thank you for your help.

Super User

https://community.powerbi.com/t5/Power-Query/Optimize-performance-at-Un-Cumulate-calculation/m-p/219...

See if those help. If not, then please provide sample data in a form that doesn't require typing data from a screenshot in manually.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors