Calcultaling sum of previous month based on a filtered month slicer

[Anonymous]

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.

[v-yuta-msft · Accepted solution]

@Anonymous ,

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.

[Anonymous]

Hello,

many thanks for the reply, it helps.

I have a question regarding totalytd, is it easier ?

Can we use it for question3 ?

Thanks,