Forum Discussion
Rolling Total
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
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
Thank you Anonymous 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=sharingThank you once again
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
7 Replies
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/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
Thank you Anonymous 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=sharingThank you once again
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
Anonymous - 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
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
