cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Last 3 years Same Month Sum and Understanding of Cumulative total

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.

```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!

1 ACCEPTED SOLUTION
Community Support

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:

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.

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.

6 REPLIES 6
Community Support

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:

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.

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.

New Member

i have sales for 3 years like 2022 and 2023 and 2024 now if i use sameperiod last year i will get 2023 sales so should i get 2022 sales please help me resovle this

New Member

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

Super User

Hi, @dax_newbie

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.

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

Super User

@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))) +

Frequent Visitor

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.