topic Running total up to certain month in DAX Commands and Tips

Running total up to certain month

rchicas3 — Tue, 13 Jun 2023 18:50:26 GMT

Hi,

Currently I have a column labled date that has all the dates from 2010 to 2030.
I have another columns with lengths for each of these dates.
I would like to get a running count of the length for up to the dates in 2014, and then every year after that up to the current year we are in I want to add 10.

This will be a line that gradually goes up in my chart.

Right now I have,

CALCULATE(SUM(table[goal])/5280,

Filter(AllSelected(table),YEAR(table[Date]) <= 2014, table[Date before prev month] <> BLANK() 

) + (YEAR(TODAY()) - 2014) * 11

but this does not give me a line that gradually goes up.

Can someone point me in the right direction?

Re: Running total up to certain month

lbendlin — Thu, 15 Jun 2023 01:21:15 GMT

Please provide sample data (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-Forum/ba-p/963216
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/1447523

Re: Running total up to certain month

Anonymous — Mon, 03 Jul 2023 06:12:20 GMT

Hi rchicas3 ,

To get a running count of the length for up to the dates in 2014, and then every year after that up to the current year we are in I want to add 10, you can use the following DAX formula:

Running Total = VAR BaseYear = 2014 VAR CurrentYear = YEAR(TODAY()) VAR RunningTotal = CALCULATE( SUM(table[Length]), FILTER( ALL(table), YEAR(table[Date]) <= BaseYear || YEAR(table[Date]) <= CurrentYear && YEAR(table[Date]) > BaseYear ) ) RETURN RunningTotal + (YEAR(TODAY()) - BaseYear) * 10

This formula first defines two variables, BaseYear and CurrentYear, which represent the years that define the start and end of the running total. The formula then calculates the running total by summing the lengths of all rows where the year is less than or equal to BaseYear, or where the year is less than or equal to CurrentYear and greater than BaseYear. Finally, the formula adds the difference between the current year and BaseYear multiplied by 10 to the running total.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.