cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calcultaling sum of previous month based on a filtered month slicer

Hi all,

 

I need your help please.

Here is my need:

1. A slicer with only 12 latest months based on current date (it means the slicer won't contain all months in my date column).

for example: if today we are on Februar then my list of months will start from 03/2018 till 02/2019, then next month it should be from 04/2018 till 03/2019 

2. Based on the selected month, I'll calculate the sum for the previous month (of that selected month)

for example if I select 01/2019 then I'll calculate the sum for 12/2018

if I select 12/2018 then I'll calculate the sum for 11/2018

3. Based on the selected month, I'll calculate the cumulated from januar of the year of that selected month till the the previous month (of that selected month)

for example if I select 01/2019, it should calculate the sum for 01/2018 til 12/2018

if I select 02/2019, it should calculate the sum for only 01/2019

if I select 04/2018, it should calculate the sum for 01/2018 till 03/2018

 

I found examples which do not filter the slicer months and/or use the selected month instead of previous month of selected one.

I tried updating these examples to my need, but it does not work for some particular months.

 

Your help are much appreciated.

 

Many thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

@MBOVEO ,

 

For question1, you can apply a visual level filter on your slicer chart which can filter the lastest 12 months. 

For question2, you may create a measure using DAX like pattern below and check if it can meet your requirement:

Result1 =
CALCULATE (
    SUM ( Table[Value] ),
    FILTER (
        Table,
        MONTH ( Table[Date] )
            = MONTH ( SELECTEDVALUE ( Table[Date] ) ) - 1
    )
)

For question3, you may create a measure using DAX like pattern below and check if it can meet your requirement:

Result2 =
CALCULATE (
    SUM ( Table[Value] ),
    FILTER (
        Table,
        YEAR ( Table[Date] ) = YEAR ( SELECTEDVALUE ( Table[Date] ) )
            && MONTH ( Table[Date] ) < MONTH ( SELECTEDVALUE ( Table[Date] ) )
    )
)

Community Support Team _ Jimmy Tao

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

2 REPLIES 2
Community Support
Community Support

@MBOVEO ,

 

For question1, you can apply a visual level filter on your slicer chart which can filter the lastest 12 months. 

For question2, you may create a measure using DAX like pattern below and check if it can meet your requirement:

Result1 =
CALCULATE (
    SUM ( Table[Value] ),
    FILTER (
        Table,
        MONTH ( Table[Date] )
            = MONTH ( SELECTEDVALUE ( Table[Date] ) ) - 1
    )
)

For question3, you may create a measure using DAX like pattern below and check if it can meet your requirement:

Result2 =
CALCULATE (
    SUM ( Table[Value] ),
    FILTER (
        Table,
        YEAR ( Table[Date] ) = YEAR ( SELECTEDVALUE ( Table[Date] ) )
            && MONTH ( Table[Date] ) < MONTH ( SELECTEDVALUE ( Table[Date] ) )
    )
)

Community Support Team _ Jimmy Tao

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

Hello,

 

many thanks for the reply, it helps.

I have a question regarding totalytd, is it easier ?

Can we use it for question3 ?

 

Thanks,

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors