cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Running total last year on the same month axis as the current year

Hello, I have some data for sales and cost with date labels. I used the following formula to calculate a running total for current year:

``````RunningTotalRaunFSRE = -CALCULATE(
SUM('Finance'[Value]),
FILTER(
ALLSELECTED('Finance'), 'Finacne'[Date] <= MAX('Finnce'[Date])
), 'Finance'[Year] = Year(TODAY())
)``````

Now what I need is basically the same but for the previous year. I must be able to put that measurement on a visualization with month x-axis along side the current year like on the following plot. Where the light grey line is the previous year.

1 ACCEPTED SOLUTION
Resolver I

What I did was Adding an if statement to the filter expression as follows:

``````RunningTotalLastYear = CALCULATE(
SUM('Finance'[Value]),
FILTER(
ALLSELECTED('Finance'),
IF(YEAR(MAX('Finnce'[Date])) = YEAR(TODAY())-1,
'Finacne'[Date] <= MAX('Finnce'[Date]),
'Finacne'[Date] <= MAX('Finnce'[Date]) - 365
)
),
'Finance'[Year] = Year(TODAY())-1
)``````

So if the MAX('Finance'[DATE]) is the previous year we can use this filter:

``'Finacne'[Date] <= MAX('Finnce'[Date])``

But if the MAX('Finance'[DATE]) is at the current year we subtract 365 days to get the last year.

Why? If we are calculating the running total for the last year but the MAX('Finance'[DATE]) is at the current year, eg. in june, We would get the total sum for last year up until june. Then after june the correct running total would be calculated. Following graphic shows this:

The correct graphic will look like this:

Where the green line is current year running total and the grey line is the previous year running total.

Resolver I

What I did was Adding an if statement to the filter expression as follows:

``````RunningTotalLastYear = CALCULATE(
SUM('Finance'[Value]),
FILTER(
ALLSELECTED('Finance'),
IF(YEAR(MAX('Finnce'[Date])) = YEAR(TODAY())-1,
'Finacne'[Date] <= MAX('Finnce'[Date]),
'Finacne'[Date] <= MAX('Finnce'[Date]) - 365
)
),
'Finance'[Year] = Year(TODAY())-1
)``````

So if the MAX('Finance'[DATE]) is the previous year we can use this filter:

``'Finacne'[Date] <= MAX('Finnce'[Date])``

But if the MAX('Finance'[DATE]) is at the current year we subtract 365 days to get the last year.

Why? If we are calculating the running total for the last year but the MAX('Finance'[DATE]) is at the current year, eg. in june, We would get the total sum for last year up until june. Then after june the correct running total would be calculated. Following graphic shows this:

The correct graphic will look like this:

Where the green line is current year running total and the grey line is the previous year running total.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.