cumulative sum for the last two years

[MCacc]

I want to create a cumulative sum in Power BI for the last two years, based on a filter selection. The fact table has a 'Year' column linked to the 'Year' field in the calendar table, which is also used for filtering. 

Cumulative Sales Last 2 Years =
VAR MaxYear = CALCULATE(
MAX(Calendar[Year]),
ALLSELECTED(Calendar)
)
VAR MinYear = MaxYear - 1

RETURN
CALCULATE(
[Total Sales],
FILTER(
ALL(Calendar),
Calendar[Year] >= MinYear &&
Calendar[Year] <= MaxYear &&
Calendar[Date] <= MAX(Calendar[Date])
)
)

However, it doesn't cumulate my sum.

Any idea how to change/improve my measure? Thank you

[mdaatifraza5556 · Accepted solution]

Hi @MCacc 

Can you please try the below DAX ?

Cumulative Sales Last 2 Years =

VAR MaxYear = CALCULATE(

    MAX(dim_date[Year]),

    ALLSELECTED(dim_date)

)

VAR MinYear = MaxYear - 1

RETURN

CALCULATE(

    [Total Sales],

    FILTER(

        ALL(dim_date),

        dim_date[Year] >= MinYear &&

        dim_date[Year] <= MaxYear &&

        dim_date[Date] <= MAX(dim_date[Date])

    )

)

If this ansewers your questions, kindly accept it as a solution and give kudos

[Anonymous]

Hi @MCacc 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Anonymous]

Hi @MCacc 
I wanted to check if you had the opportunity to review the information provided by @danextian and @mdaatifraza5556 . Please feel free to contact us if you have any further questions. If their response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Anonymous]

Hi @MCacc 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[danextian · Accepted solution]

Hi @MCacc 

I'm wondering why your fact table is linked to calendar on the year and not the date column and if they're linked on year, what is the use of adding Calendar[Date] <= MAX(Calendar[Date]) to your measure?

That aside, you can instead use DATESINPERIOD

Last 2 years = 
CALCULATE (
    SUM ( factTable[Value] ),
    DATESINPERIOD ( CalendarTable[Date], MAX ( CalendarTable[Date] ), -2, YEAR ),
    REMOVEFILTERS ( CalendarTable )
)

[mdaatifraza5556 · Accepted solution]

Hi @MCacc 

Can you please try the below DAX ?

Cumulative Sales Last 2 Years =

VAR MaxYear = CALCULATE(

    MAX(dim_date[Year]),

    ALLSELECTED(dim_date)

)

VAR MinYear = MaxYear - 1

RETURN

CALCULATE(

    [Total Sales],

    FILTER(

        ALL(dim_date),

        dim_date[Year] >= MinYear &&

        dim_date[Year] <= MaxYear &&

        dim_date[Date] <= MAX(dim_date[Date])

    )

)

If this ansewers your questions, kindly accept it as a solution and give kudos