cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emmaclarke83
Helper I
Helper I

relative date slicer - last 12 months

Does anyone know how to write the DAX required to show something similiar to the relative date slicer - last 12 months?

 

I have a custom calender and would like to show the last 12 periods from the period selected in a date slicer.

13 REPLIES 13
waltheed
Solution Supplier
Solution Supplier

Hi emmaclarke83, 

 

I usually add some columns for this purpose to the date dimension in my data model. 

 

e.g. 

Relative Year = YEAR ( 'Date'[Date] ) - YEAR ( TODAY () )

Relative Month = if([Date]> Today(); datediff( today(); [Date]; MONTH); -1 *  datediff([Date]; today();MONTH))

Relative Week = if([Date]> Today(); datediff( today(); [Date]; Week); -1 *  datediff([Date]; today();Week))

 

 

 

Of course, this only works if you process the model regularly. 

 

I add these as page level filters to some pages that should show data for only the current year (filter by 0), or the last 12 months (filter between 0 and -12) .

 

You can further enhance this by adding columns with user frienldy words to the data dimension, like 0 = Current Year, etc. 

 

Hope this helps. 

 

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Power BI User Group Netherlands

Thank you, this is exactly what i have been looking for as i have a matrix table that i have wanted to filter the past N full months with the current Month.

 

Relative Month = if([Date]> Today(); datediff( today(); [Date]; MONTH); -1 *  datediff([Date]; today();MONTH))Date Filter.png

 

Hi thanks.  The problem is we have a custom calendar (4,4,5 week) with periods not months, so the standard time intelligence doesn not work.

Can you post your sample custom calendar?


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi Greg

 

See link for file.  Please also note that every few years we have a 53rd week.

 

Thanks

 

Emm

 

Custom Date Calendar

@emmaclarke83- Luckily I have been doing a ton of work around rolling months, weeks, etc. So, I think I have this and it is based off of the same pattern that I have been using.

 

Here are the 5 measures you need:

 

First Day of Period

First Day of Period = 
VAR WorkingDate = MAX('Calendar'[Date])
//We are working with a customer calendar so let's use it
VAR tmpCalendar = ALL('Calendar')
VAR WorkingPeriod = MAXX(FILTER(tmpCalendar,[Date]=WorkingDate),[Period])
VAR WorkingYear = MAXX(FILTER(tmpCalendar,[Date]=WorkingDate),[YearNumber])
VAR tmpCalendar1 = FILTER(tmpCalendar,[YearNumber]=WorkingYear&&[Period]=WorkingPeriod)
RETURN MINX(tmpCalendar1,[Date])

Last Day of Period

Last Day of Period = 
VAR WorkingDate = MAX('Calendar'[Date])
//We are working with a customer calendar so let's use it
VAR tmpCalendar = ALL('Calendar')
VAR WorkingPeriod = MAXX(FILTER(tmpCalendar,[Date]=WorkingDate),[Period])
VAR WorkingYear = MAXX(FILTER(tmpCalendar,[Date]=WorkingDate),[YearNumber])
VAR tmpCalendar1 = FILTER(tmpCalendar,[YearNumber]=WorkingYear&&[Period]=WorkingPeriod)
RETURN MAXX(tmpCalendar1,[Date])

Rolling Period Start

Rolling Period Start = 
VAR NumPeriods = 12
VAR WorkingDate = MAX([Date])
VAR PeriodsBack = 11
VAR tmpCalendar = ALL('Calendar')
VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"PeriodStart",[First Day of Period],"PeriodEnd",[Last Day of Period])
VAR WorkingPeriod = MAXX(FILTER(tmpCalendar,[Date]=WorkingDate),[Period])
VAR WorkingYear = MAXX(FILTER(tmpCalendar,[Date]=WorkingDate),[YearNumber])
VAR LookupPeriod = WorkingPeriod-PeriodsBack
VAR LookupPeriod1 = IF(LookupPeriod<=0,LookupPeriod+NumPeriods,LookupPeriod)
VAR LookupYear = IF(LookupPeriod<=0,WorkingYear-1,WorkingYear)
VAR tmpCalendar2 = FILTER(tmpCalendar1,[Period]=LookupPeriod1&&[YearNumber]=LookupYear)
VAR retValue = MAXX(tmpCalendar2,[PeriodStart])
RETURN IF(ISBLANK(retValue),MINX(tmpCalendar1,[PeriodStart]),retValue)

 

Rolling Period End

Rolling Period End = 
VAR NumPeriods = 12
VAR WorkingDate = MAX([Date])
VAR PeriodsBack = 0
VAR tmpCalendar = ALL('Calendar')
VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"PeriodStart",[First Day of Period],"PeriodEnd",[Last Day of Period])
VAR WorkingPeriod = MAXX(FILTER(tmpCalendar,[Date]=WorkingDate),[Period])
VAR WorkingYear = MAXX(FILTER(tmpCalendar,[Date]=WorkingDate),[YearNumber])
VAR LookupPeriod = WorkingPeriod-PeriodsBack
VAR LookupPeriod1 = IF(LookupPeriod<=0,LookupPeriod+NumPeriods,LookupPeriod)
VAR LookupYear = IF(LookupPeriod<=0,WorkingYear-1,WorkingYear)
VAR tmpCalendar2 = FILTER(tmpCalendar1,[Period]=LookupPeriod1&&[YearNumber]=LookupYear)
RETURN MAXX(tmpCalendar2,[PeriodEnd])

Example Measure for an Aggregation

Measure = 
VAR rollingPeriodStart = [Rolling Period Start]
VAR rollingPeriodEnd = [Rolling Period End]
VAR tmpTable = ALL('Calendar')
VAR tmpTable1 = FILTER(tmpTable,[Date]>=rollingPeriodStart&&[Date]<=rollingPeriodEnd)
RETURN AVERAGEX(tmpTable1,[YearNumber])

Let me know if this works for you.

 

 

 

 

 

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi Greg

 

Thanks for your reply.  The first 4 measures work great.  But the last one for the aggregation is restricted by the period selected in my filter.

On another note, what I would like to do is show these results on a column chart by period.

 

I have attached a link to my power bi file.

 

power bi file

@emmaclarke83- Sorry have been out for a bit. I downloaded your PBIX file. I believe what you want to do is to use ALLEXCEPT instead of ALL perhaps? So in place of ALL filter, use ALLEXCEPT(Dates[Year]) perhaps? Seems like your Period slicer is blanking the column chart and I'm not entirely certain as to why.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi Greg

 

I have turned off interactions on the period filter atm so it just shows the full year.  I will try your suggestion.  Thanks

The issue is that once you have the Period slicer filter that visual, then it will filter the visual to only that period and no other periods. But, if you don't filter the visual with Period then it will obviously have zero effect on the visual. So, that's quite a pickle.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Do you think there is any solution to what I am looking for?

Yeah, I've been noodling on that and haven't come up with anything yet. Let me contemplate that one and see if I have any bright ideas. @Phil_Seamark@Seth_C_Bauer either of you guys have any ideas on this one?


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
Greg_Deckler
Super User
Super User

Check out my Rolling Months Quick Measure here:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors