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.
Please 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
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.