cancel
Showing results for
Did you mean:
Frequent Visitor

## Calcultaling sum of previous month based on a filtered month slicer

Hi all,

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.

Many thanks.

1 ACCEPTED SOLUTION

Accepted Solutions Community Support

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.

2 REPLIES 2 Community Support

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.

Frequent Visitor

Hello,

many thanks for the reply, it helps.

I have a question regarding totalytd, is it easier ?

Can we use it for question3 ?

Thanks, Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### 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
Users online (1,413)