Skip to main content
cancel
Showing results for 
Search instead 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

Reply
zawlh
Helper I
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.

Daily From Running Total.PNG

I would like to request how to add a new column containing daily data.
Thank you.

4 ACCEPTED SOLUTIONS

Your example starts here:

AlexisOlson_0-1637435633525.png

 

I'll add a new index column [Index1] = [Index] + 1 and then self merge:

AlexisOlson_1-1637435749966.png

 

Expand Total_Values

AlexisOlson_2-1637435790691.png

 

Define a new [Daily_Values] column as the difference between the total and the previous total.

AlexisOlson_3-1637435915900.png

 

Result:

AlexisOlson_6-1637436115580.png

 

 

AlexisOlson_4-1637436068852.png

 




File attached.

View solution in original post

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.

Untitled.png


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

View solution in original post

Hello @zawlh 

 

The image and DAX code are given below for your quick reference.

 

Kumail_0-1637492220648.png

 

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.

View solution in original post

v-yanjiang-msft
Community Support
Community Support

Hi @zawlh ,

According to your description, heres my solution.

vkalyjmsft_0-1637548765200.png

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.

View solution in original post

13 REPLIES 13
v-yanjiang-msft
Community Support
Community Support

Hi @zawlh ,

According to your description, heres my solution.

vkalyjmsft_0-1637548765200.png

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.

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

zawlh
Helper I
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.

Hello @zawlh 

 

The image and DAX code are given below for your quick reference.

 

Kumail_0-1637492220648.png

 

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.

Your shared file works. Thank you for your help.

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

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

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.

Untitled.png


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

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

You are welcome.


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

Your example starts here:

AlexisOlson_0-1637435633525.png

 

I'll add a new index column [Index1] = [Index] + 1 and then self merge:

AlexisOlson_1-1637435749966.png

 

Expand Total_Values

AlexisOlson_2-1637435790691.png

 

Define a new [Daily_Values] column as the difference between the total and the previous total.

AlexisOlson_3-1637435915900.png

 

Result:

AlexisOlson_6-1637436115580.png

 

 

AlexisOlson_4-1637436068852.png

 




File attached.

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

AlexisOlson
Super User
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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors