Custom Relative Month Filter

[adiljamal]

Hey Community,

TLDR:  Given monthly data of past few years, how can I create a relative month filter, where I have the option to filter data of last X month (where my reference month is not today, bu the latest data available)?

I have monthly data of generation starting 2016, and as of now the latest data is of May 2023 (which keeps on getting updated as we move ahead).

I'm trying to visualize total generation value for last X month, where user has option to select the X. Considering right now, I have data till May 2023, my last month would be May 2023.

The by default relative date slicer in Power BI considers last day as today , and hence, I'm not getting the desired result.

As you can see, the relative date is considering today as the benchline, plus since I have only monthly data, the option of 'weeks' and 'days' do not make sense for my visualization.

I need something like this:

Just a dropdown of a number of months, and based on that, my generation graph automatically adjusts.

To create a relative month filter, I tried the following approach.

Disconnected_Date = CALENDAR(FIRSTDATE('Actual Date'[Date]),LASTDATE('Actual Date'[Date]))

Selected_date = SELECTEDVALUE(Disconnected_Date[Date])

 Then I created a table for number of months.

Selected_Period = SELECTEDVALUE(Month_table[Months])

And finally, I created this filter.

Date_filter = 
var _selected =[Selected_date]
var _date=min('Actual Date'[Date])
var _period= [Selected_Period]*30
var _daterange=_selected-_period
var _filter=if(_date<=_selected,if(_date>=_daterange,1,0),0)
return
_filter

The above filter works on days perfectly fine, but for months, I had to multiply it by 30. So, I'm getting correct results for 7 months, but after that, if I select 8 months, I am getting 7-month values. I believe multiplying it by 30 is not the best option.

I wanted to know how I can use the above filter on month level rather than on days level?

@lbendlin 

[lbendlin]

The standard approach is similar to

- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app

[Ashish_Mathur]

Hi,

Share some data to work with.

[amitchandak]

@adiljamal , do all calculation based on date

Like last 5 days, use your period variable

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max -5
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Last 12 months

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

you can use date as date add

https://community.powerbi.com/t5/Community-Blog/Date-as-Dateadd-Decoding-Date-and-Calendar-5-5-Power...