Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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))
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?
Hi Greg
See link for file. Please also note that every few years we have a 53rd week.
Thanks
Emm
@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.
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.
@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.
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.
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?
Check out my Rolling Months Quick Measure here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |