Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
dax_newbie
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.

 

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!

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1618298261981.png

 

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:

v-robertq-msft_1-1618298261988.png

 

 

And you can get what you want, like this:

v-robertq-msft_2-1618298261993.png

 

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.

View solution in original post

6 REPLIES 6
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1618298261981.png

 

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:

v-robertq-msft_1-1618298261988.png

 

 

And you can get what you want, like this:

v-robertq-msft_2-1618298261993.png

 

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.

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 

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

Jihwan_Kim
Super User
Super User

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.

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.


Go to My LinkedIn Page


amitchandak
Super User
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))) +

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

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. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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