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

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

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

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

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!

Highlighted
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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors