Last 3 years Same Month Sum and Understanding of Cumulative total

[dax_newbie]

Hi Power BI Community,

I have two doubts:

1. There is a table with date, amount column. The time period of the table spans around 6 years. I am using table visual with date hierarchy (only year and month in axis). My requirement is to calculate the 3 years total for the same month.

Let's assume april row:

2020 April      => 2020 April + 2019 April + 2018 April.

I wrote a DAX formula:

CALCULATE(
      SUM('Table'[Amount]),
      FILTER(
            DATESINPERIOD('Table'[Date],LASTDATE('Table'[Date]),-3,YEAR), 
            MONTH('Table'[Date]) = MONTH(LASTDATE('Table'[Date]))
       )
)

But it brings the value for each month. I mean whatever the value for 2021 April, it is bringing that one. I think I am missing another filter condition somewhere, but couldn't figure out.

2. I was reading about Cumulative total and found this formula:

Running Total MEASURE = 
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    FILTER (
        ALL ( 'All Web Site Data (2)' ),
        'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
    )
)

But I couldn't understand one part. FILTER works in row context, so it goes row by row. But how does MAX bring the value for the current filter context. And also how does the 'All Web Site Data (2)'[Date] <= work if the filter context is still in place?

Thanks!

[v-robertq-msft · Accepted solution]

Hi, @dax_newbie 

According to your description and requirement, I think you can create a measure that just uses variables and the Calculate() function to achieve this, you can try this measure:

I’ve also created some test data based on your description:

Last 3 years Same Month Sum =

var _selectedyear=YEAR(MIN('Table'[Date]))

var _selectedmonth=MONTH(MIN('Table'[Date]))

return

CALCULATE(SUM('Table'[Amount]),

FILTER(

    ALL('Table'),

    YEAR([Date])>=_selectedyear-2&&

    YEAR([Date])<=_selectedyear&&

    MONTH([Date])=_selectedmonth))

Then create a slicer to place the year and month column, a table visual to place this measure:

And you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[gdmolina]

This really helped me to get a similar calculation. Thank you so much!!

[Jihwan_Kim]

Hi, @dax_newbie 

Please try the below measure.

Same Month 3 Years Total =
CALCULATE (
[Amount],
FILTER ( ALL ( Dates ), Dates[Month] = MAX ( Dates[Month] ) ),
DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -3, YEAR )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[amitchandak]

@dax_newbie , with April selected  you can try measure like example measure

Assume april 2021 is selected

MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) +

CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-24,MONTH))) +CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-36,MONTH)))

[dax_newbie]

Hi @amitchandak Thanks for the reply. But I am trying to come up with a solution that can be easily changed. Let's assume if I wanted to make last 10 years total, I would need to add lot of DATESMTD function.