So I have a date dimension and I have the month offset in there from Current Day. What I want is to be aboe to add a slicer to the report that will offer the opportunity to select date periods
“Current Month”
“Previous Month”
“Last Three Months”
“Last 6 Months”
“Last 13 Months”
It had occured to me that I could just add these 5 as a column to the date table and have a zero or one if the date falls within that range. However I want something more flexible - so in the future I dont have to add a whole load of columns to the date table.
I was thinking of having a seperate table Called Period Selector that could have
Period Selector StartMonthOffset EndMonthOffset
Current Month 0 1
Previous Month 1 2
Last 6 Months 0 5
Cool idea. I think you’re on exactly the right track with the disconnected table. I wouldn’t connect that to the Date table. Instead, just use LOOKUPVALUE to grab the start and end offsets from your disconnected table based on your slicer choice:
Lookup Offset Start =
LOOKUPVALUE(
'Disconnected Date Periods'[StartOffset],
'Disconnected Date Periods'[Period Selector],
[Harvest Period Selector]
)
Exact same approach for the End Offset lookup.
You can then use these measures in your Time intelligence measures to set the filter conditions to the appropriate offsets.
I hope that’s helpful. Solution file attached below.
I know @BrianJ has already supplied a solution but you could also explore this technique and extend the Periods already provided with any number of customized Periods you define.
This is one of those really interesting questions where if you ask 10 Power BI users, you’ll probably get at least 10 different approaches. Here’s another interesting way of tackling this problem that I remember seeing on Reed Havens’ channel a few months back:
Ok,
so went through the youtubes from Haven and I see and understand what it is doing.
However my desire is that
if I have a table of sales for two years (730 days) that I can use a visual to filte the rows returned to just current Month rather than change the sales value if it is within that period.
To do this manually I place an advanced filter on the date dimension where RelativeMonth <3 and this limits the visual to show the current month and values (as relative month = 0), Previous Month and values (relative month = 1, Previous Previous Month and values (as relative Month = 2)
Still looking for the right solution Perhaps saying slicer was the wrong wording.
This Date table M function contains Offsets for several Date dimensions.
.
These Offsets are evaluated each time the Date table query is refreshed. The basic principle behind them is that past periods are negative, future periods positive and current periods equal to 0.
This allows you to put an Between Offset Slicer on your report page OR in the Filter pane.
Without changing the current Slicer selection depicted above, as time progresses and we move into July it will automatically show months Feb-Jul.
@Melissa
Thanks. My date table iscreated via M Code and I believe that means Relative Month will update. I just want it to be click for certain periods defined by the organisation.
My trials failed on two fronts that will happen to other people too.
My Sales data does not have todays date in so showing current period based on what the date is and not what the sales data has does not work.
My date table has future dates so if I want last three periods then Relative Month <= 2 brings in all of the months with a minus number as relevant months.
Point number 2 may seem inconsequential unless you have a system with future dated sales.
Then what should be considered todays date in your report? Is that the date of the scheduled refresh
or …
.
You could create a filter combination with the Filter pane AND a Slicer on your report page.
Can you share a sample so I can see the logic for your Relative Month and everything else that’s in play?
Brian did a video on masking your data if that is a concern.
Added a pic of the current solution. This works as I want (but not quite 100%). The small flaw is it filters if there is a value in ‘Measure within Selected Period’ So if there are 31 days in the previous sales period but say that included a bank holiday with zero sales you would not see a row in the report with zero sales as they are filtered out.
I was hoping that I could filter on Relative Month <= ‘Measure for Upper limit’ and Relative Month >= ‘Measure for lower limit’ . However it only seems to allow literal values i.e Relative Month <= 4
I am sure the last piece of the jigsaw must be quite straightforward.
Thanks
E
Love it!
