Running total for a specific period

[Kate_24]

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

[v-zhangti · Accepted solution]

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.

[Kate_24]

Thanks s lot! It worked!

[foodd]

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

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

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.

[Kate_24]

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

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the solution file.

Hope this helps.

[v-zhangti · Accepted solution]

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.