Calculating Daily values from Cumulative Total

[zawlh]

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.

[AlexisOlson · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[Kumail · Accepted solution]

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.

[v-yanjiang-msft · Accepted solution]

Hi @zawlh ,

According to your description, here’s 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.

[zawlh]

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

[zawlh]

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.

[Kumail · Accepted solution]

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.

[zawlh]

Your shared file works. Thank you for your help.

[zawlh]

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

[Kumail]

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

[Ashish_Mathur · Accepted solution]

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.

[zawlh]

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

[Ashish_Mathur]

You are welcome.

[AlexisOlson · Accepted solution]

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.

[zawlh]

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

[AlexisOlson]

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.