Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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.

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
https://www.linkedin.com/in/excelenthusiasts/
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

If this answers your query, mark it as the solution.

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

If this answers your query, mark it as the solution.

Kudos are appreciated.

Helper I

Your shared file works. Thank you for your help.

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

The working pbix file is added to the drive. Feel free to download it for your reference.

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
https://www.linkedin.com/in/excelenthusiasts/
Helper I

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

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.

Helper I

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

Super User

I've answered similar questions before:
https://community.powerbi.com/t5/Power-Query/Optimize-performance-at-Un-Cumulate-calculation/m-p/219...

https://stackoverflow.com/questions/54814815/add-column-of-previous-values-from-table-of-tables-in-p...

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.

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors