Date Slicer that show last N Months including current month

[Dodgywheel]

HI,

I have a report to look at the last N months of data, but want to include current month, the filter for selecting the amount of months needs to be simple like the standard slicer 

Does anyone know a way to do this .

Thanks

[v-shex-msft]

Hi @Dodgywheel,

What type of 'include current month' you mean? Does force add the current month to your calculation range or start/end with the current month? Please share more detail information to help us clarify your requirement.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Dodgywheel]

Hi @v-shex-msft , 

So for example , my graph currently has the slicer set to show Last 6 months,  so the data it shows are as below

As you can see the months covered are DEC - MAY inc. what i would like is to show JAN - JUN as the last 6 months , so the current month is included, i know i can do this with a slicer selecting dates in between the two months but for ease of use when presenting data i want to keep the select last N months slicer.

Thanks

Dale

[v-shex-msft · Accepted solution]

Hi @Dodgywheel,

You can create a what-if parameter table with numeric values, then use it as slicer to choose month numbers that you want to be filtered.
Then you can write a measure formula to extract selected value to compare current table values and return tag. After these states, you can use this measure on the 'visual level filter' to filter records.

Measure =
VAR selected =
    MAX ( Selector[Number] )
VAR currDate =
    MAX ( table[date] )
RETURN
    IF (
        currDate
            IN CALENDAR (
                DATE ( YEAR ( currDate ), MONTH ( currDate ) - selected, DAY ( currDate ) ),
                currDate
            ),
        "Y",
        "N"
    )

Notice: selector means the what-if table that used to selected month numbers.
Regards,

Xiaxoin Sheng

[Anonymous]

No this is way too much work and maintenance. Surely Power BI funcitonality needs to cater - this is very straight forward . Most companies will want to see what is performance of last N months and include current month to date. So today is 20 April, I want to see APRIL mtd totals, and then the last N full months for comparison. Much like the DAYS relative date fiter, just add in a INCLUDE THIS MONTH tick box. 😉 simple.

[amitchandak]

@Dodgywheel , When you use date slicer of visual level or page level you have the option to include today.

You can add this month /MTD data to the measure

Add this

MTD QTY forced=
var _max = today()
var _min = eomonth(today(),-1)+1
return
calculate(Sum('order'[Qty]),filter(all('Date'),'Date'[Date]<=_max && 'Date'[Date]>=_min))

[Dodgywheel]

Hi @amitchandak 

I have the filter set to include Today but it doesn't show current month, my measure that i am graphing per month is 

OEE % = 'pro vw_ProductionOEE'[OEE Loss] * 100  

Is the below correct for how it fits with the formula you shared or is it a seperate new measure. 

MTD QTY forced=
var _max = today()
var _min = eomonth(today(),-1)+1
return
calculate('pro vw_ProductionOEE'[OEE Loss] * 100  ),filter(all('Date'),'Date'[Date]<=_max && 'Date'[Date]>=_min))

I am a novice to Power BI so still teaching myself slowly

Thanks for the help

Dale