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.
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
Community SupportHi, 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.