Rolling Total

[batman]

Hello Everyone, 

I need some assistance with my rolling total, I have a date hierarchy and my rolling total works for days, months and quarters but for some strange reason once I role up the hierarchy to the year it shows only the last months rolling total for the year. 

Below is my formula 

Price Rolling Total =

CALCULATE ( SUM ( NewMeasures[Price]), DATESQTD('Calendar'[Date] ))


Thank you 

[v-yiruan-msft · Accepted solution]

Hi @batman ,

You can create another new measure as below and put this new measure to replace the original measure onto the table visual as below screenshot...

Measure = SUMX ( VALUES ('Calendar'[Date] ), [Price Rolling Total] ) 

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. And share the visual field settings as below screenshot. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[batman · Accepted solution]

Thank you @v-yiruan-msft and @ValtteriN, I really appricate all the help. Below is what I am trying to do, I am trying to get my Rolling Cost Total QTD to qual 7922 instead of 3342. 

Please see link for the PBIX Workbook: https://drive.google.com/file/d/1V6gJRZsA5vEZ074OhKwSwZAW4BXrryBg/view?usp=sharing

Thank you once again 

[batman · Accepted solution]

Thank you @v-yiruan-msft and @ValtteriN, I really appricate all the help. Below is what I am trying to do, I am trying to get my Rolling Cost Total QTD to qual 7922 instead of 3342. 

Please see link for the PBIX Workbook: https://drive.google.com/file/d/1V6gJRZsA5vEZ074OhKwSwZAW4BXrryBg/view?usp=sharing

Thank you once again 

[batman]

@v-yiruan-msft  - My apologies, please see the following link https://drive.google.com/file/d/1V6gJRZsA5vEZ074OhKwSwZAW4BXrryBg/view?usp=sharing

https://drive.google.com/file/d/1V6gJRZsA5vEZ074OhKwSwZAW4BXrryBg/view?usp=sharing 

If it doesnt work for some reason, I will upload it to dropbox. I am not a super user so I can not attach. 

Thank you once again 

[v-yiruan-msft · Accepted solution]

Hi @batman ,

I updated your sample pbix file, please find the details in the attachment.

1. Create a measure as below

Measure = 
SUMX (
    GROUPBY ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Quarter] ),
    [Rolling Cost Total QTD]
)

2. Put the above measure onto the matrix to replace the original measure [Rolling Cost Total QTD]

Best Regards

[batman]

Thank you so much for taking your time and updating the workbook, a quick question for you what if I wanted everything to stay the same when rolled up so YEAR Same, Quarter same, Months Same but when drilled down to the days have a rolling total going? 

[v-yiruan-msft]

Hi @batman ,

It seems that I have no access to your shared file. Could you please grant me the proper access to the file? Or you can refer the following links to share the file with us. Thank you.

How to upload PBI in Community

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @batman ,

You can create another new measure as below and put this new measure to replace the original measure onto the table visual as below screenshot...

Measure = SUMX ( VALUES ('Calendar'[Date] ), [Price Rolling Total] ) 

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. And share the visual field settings as below screenshot. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[ValtteriN]

Hi,

This has to do with the quarter changing and filter context.

In short:

Data:

Without row context this measure will return latest quarter:

Quarter = QUARTER(MAX('Table (2)'[Date]))

Example:
Notice that on a yearly level quarter = 4

Because of this QTD will return the lastest quarter values (only October values).

I recommend checking this article about the running totals topic: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/