Skip to main content
cancel
Showing results for 
Search instead 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

Reply
314mp_M0th4
Resolver I
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.

314mp_M0th4_0-1687270406174.png

 

1 ACCEPTED SOLUTION
314mp_M0th4
Resolver I
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:

314mp_M0th4_0-1687348462975.png

The correct graphic will look like this:

314mp_M0th4_1-1687348702382.png

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

View solution in original post

1 REPLY 1
314mp_M0th4
Resolver I
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:

314mp_M0th4_0-1687348462975.png

The correct graphic will look like this:

314mp_M0th4_1-1687348702382.png

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

Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.