Date period table joined to date dim

[Anonymous]

Hi all. We have what is a standard feel model with a bunch of fact tables and a date dimension. The fact tables have some standard measures such as "sales value" or "sales volume" which work well with our hierarchies.

Our challenge is we have date periods like "Latest week", "Previous Week", "Week to date", "Latest 4 weeks". These may or may not overlap ie we can't bake these in to the date dimension as a single new attribute of a date.... As our model covers a number of business areas and has numerous measures for each area creating these variations as different measures is painful.

The initial idea we pursued was to have a lookup table with these periods in and a relationship with the date dimension. Our thinking was users could include the "date period" table in visualisations which would then roll up the measures by that period. Appreciate this creates a sort of many to many relationship with the date dimension as a bridging table.

The above seems a reasonable idea, easy too articulate but it doesn't work out of the box. Firstly I'm wondering if we're doing something wrong?

Secondarily, I just don't think this should be that difficult. Having something you roll up to which overlaps must be common pattern but the way to do it is alluding us. So any hints would be gratefully received.

Thanks, RobJ.

[CheenuSing]

Hi @Anonymous,,

You can create a filter on the Date from the calendar table and set it to relative . This will then give an option to choose last /next, number of , months , days, year etc.

A sample screen shot

    

Let me know if this works for you .

Cheers

CheenuSing

[Anonymous]

Thanks @CheenuSing, I've investigateed the relative filter and I've found it only filters from today to a point.

It can also be confusing for users where they might use it to filter, say, the last month but have measures rolled up by week. This then invariably cuts a through a week.

[Anonymous]

This solution should result in these two table matching:

 

Trying to find a way to upload the pbix for this.

[CheenuSing]

Hi @Anonymous

You can upload the pbix to OneDrive or GoogleDrive and share the link here.

Cheers

CheenuSing

[Anonymous]

Thanks @CheenuSing

Link to a demo PBIX

https://1drv.ms/u/s!ADsEi5ywZDJegqpv

[CheenuSing · Accepted solution]

Hi @Anonymous

I looked at the pbix file.  

Upon checking I notice that the Relationship between Date and Period is Single direction.  And hence the system does not filter the records os Sales by period-date and shows the total of sales.

When I changed the filter direction between Period and Date to Both I could get the result desired.

However I noticed that the Mtd in the period table is only till Jan 20 and hence the total for Mtd is 128 and not 192 as per the month wise table based on Date[Date] which has values till Jan 30.

If this solves your issue , please accept this as a solution and also give KUDOS.

Cheers

CheenuSing 

[Anonymous]

Thanks @CheenuSing 🙂