Forum Discussion
Running total for a specific period
Hi, Kate_24
You can try the following methods.
Measure:
Cumulative = Var _Sum=CALCULATE(SUM('Table 1'[Sales]),FILTER(ALL('Table 2'),[Month_U]<=SELECTEDVALUE('Table 2'[Month_U]))) Var _Maxmonth=CALCULATE(MAX('Table 1'[Month]),ALL('Table 1')) Return IF(SELECTEDVALUE('Table 2'[Month_U])>_Maxmonth,BLANK(),_Sum)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Please find attached the solution file.
Hope this helps.
Community ChampionPlease provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.
Unfortunately OneDrive, Dropbox, Google Drive or Wetransfer are websites blocked by my Company.
Tables I have:
Month Sales
01/01/2023 152.535,00 € 01/02/2023 94.820,00 € 01/03/2023 40.693,00 € 01/04/2023 123.050,00 € 01/05/2023 18.207,00 € 01/06/2023 138.413,00 € 01/01/2023 137.746,00 € 01/02/2023 186.861,00 € 01/03/2023 168.635,00 € 01/04/2023 191.546,00 € 01/05/2023 85.014,00 € 01/06/2023 182.044,00 € 01/01/2023 103.580,00 € 01/02/2023 98.778,00 € 01/03/2023 134.944,00 € 01/04/2023 43.533,00 € 01/05/2023 111.179,00 € 01/06/2023 140.790,00 € Table 2
Month_U
01/01/2023 01/02/2023 01/03/2023 01/04/2023 01/05/2023 01/06/2023 01/07/2023 01/08/2023 01/09/2023 01/10/2023 01/11/2023 01/12/2023 This is what I get after I create the quick measure "Running total" by month
Month Cumulative Jan 393.861,00 € Feb 774.320,00 € Mar 1.118.592,00 € Apr 1.476.721,00 € May 1.691.121,00 € Jun 2.152.368,00 € This is what I would like to have:
Month Cumulative Jan 393.861,00 € Feb 774.320,00 € Mar 1.118.592,00 € Apr 1.476.721,00 € May 1.691.121,00 € Jun 2.152.368,00 € Jul Aug Sep Oct Nov Dec I would like that the sale cumulative graph would stop in June in order to let me add a forecast for next months.
I hope it is clear,
Thanks
Caterina
v-zhangtiCommunity Support
Hi, Kate_24
You can try the following methods.
Measure:
Cumulative = Var _Sum=CALCULATE(SUM('Table 1'[Sales]),FILTER(ALL('Table 2'),[Month_U]<=SELECTEDVALUE('Table 2'[Month_U]))) Var _Maxmonth=CALCULATE(MAX('Table 1'[Month]),ALL('Table 1')) Return IF(SELECTEDVALUE('Table 2'[Month_U])>_Maxmonth,BLANK(),_Sum)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Ashish_Mathur
Super User
Hi,
Please find attached the solution file.
Hope this helps.
