Forum Discussion
Running total for a specific period
Dear all,
I am new user of Power Bi and I am not good at DAX.
I created a quick measure "Running total" in order to display the cumulative sales across months.
The available data are until June, therefore for the following months (from July until September) a straight line is displayed.
How can I get rid of that straight line (that I would like to replace with a Forecast)?
In other words I don't know how to stop the cumulative calculations in June (included).
I have a sheet containing the "months" and the "sales"; another sheet containing the table ("Month_U") with all the months in the year (January from December). There is a relationship between the "months" and "Month_U".
The quick measure I created has the "Sales" as "Base Value" and the "Month_U" as "Field".
I tried to use "months" as "Field" but the quick measure does not calculate the cumulative values.
Please, can anyone help?
thank you
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.
5 Replies
- foodd
Community Champion
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
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.
Thanks s lot! It worked!
