cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emmaclarke83 Regular Visitor
Regular Visitor

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
Super User
Super User

Re: relative date slicer - last 12 months

Check out my Rolling Months Quick Measure here:

 

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

waltheed
Advisor

Re: relative date slicer - last 12 months

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
Power BI User Group Netherlands
emmaclarke83 Regular Visitor
Regular Visitor

Re: relative date slicer - last 12 months

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.

Super User
Super User

Re: relative date slicer - last 12 months

Can you post your sample custom calendar?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

emmaclarke83 Regular Visitor
Regular Visitor

Re: relative date slicer - last 12 months

Hi Greg

 

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

 

Thanks

 

Emm

 

Custom Date Calendar

Super User
Super User

Re: relative date slicer - last 12 months

@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.

 

 

 

 

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

emmaclarke83 Regular Visitor
Regular Visitor

Re: relative date slicer - last 12 months

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

Super User
Super User

Re: relative date slicer - last 12 months

@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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

emmaclarke83 Regular Visitor
Regular Visitor

Re: relative date slicer - last 12 months

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,239)