cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.