Multiple date dimensions - time intelligence

[julianwi]

Hallo everyone,
We are currently working in a project where the customer made a request where we are not sure if we found the best solution.
The powerpivot model is about sales data. The sales data have multiple date dimensions (mainly shipment date and posting date). The customer want’s to be able to use multiple date dimensions in the same report.
We created a couple of time-intelligence-functions (such as Revenue Previous Year = IF(ISERROR(FIRSTDATE(SAMEPERIODLASTYEAR('Dim Posting Date’[Posting Date]])));BLANK();
CALCULATE([Revenue]; ALL('Dim Posting Date‘); SAMEPERIODLASTYEAR('Dim Posting Date’[Posting Date]))
)

This is working when the customer is filtering in the posting date. However if they decide that they want to slice and dice according to the shipment-date the previous-year measures of course won’t work.
I came up with the following possible solutions:

1. Do not allow multiple date dimensions on one report - would be a big limitation
2. Create multiple (one per date dimension) Previous Year-Measures – this would not be very comfortable for users
3. Change the measures so that no PY-value would be displayed when more than one date dimension is on the pivot. The measure however would have to be smart enough to change to filtering based on the chosen date dimension

Can anyone think of other solutions? How would #3 be implemented? I read about changing relationships by using USERELATIONSHIP but I am not sure how to adapt that function to this problem.


Thanks in advance!


Regards,
julianwi

 

[paivers]

Julianwi, I think option 1 is the most common approach. The issue is that if you have multiple date dimension the meaning of say "January" becomes ambiguous, is it products that shipped in January, or posted. Its can't be both. Typically any given report looks at shipment or postings but not both. Perhaps this could be addressed by measures such as LeadTimeBeforePosting, and you could put metrics on by shipment date to see if that is changing. I just can't imagine two calendars in the same pivot concurrently. - Pete

 

[julianwi]

Dear paivers, thank you for your response! We actually make sure that only one Date-Dimension is used at once to avoid that ambiguity that you mentation- but the user is able to switch between the different date dimensions what is very important for us. Regards, julianwi

 

[paivers]

OK, I thought you meant in one report based on your option 3. Yes, I do that a lot and you have to use multiple measures (your option 2) as each measure will calculate according to the related dimension. Its a matter of good naming practices to make is easier for your users, and I see you have your date dim members appropriately named such as Posting Date. Of course my users would prefer one data dimension, but do understand why there are multiple. Occasionally they forget and use the wrong one and the measures start repeating values so they spot it quickly.

 

[powerpivotpro]

I would consider using a "disconnected slicer" to let the user pick which date dimension they want to use, have your date measures detect that selection, and then, via a SWITCH or an IF, change the measure behavior. I have not personally employed USERELATIONSHIP in this manner but it sounds promising, let me know if it works out ok?

 

[julianwi]

Hi Rob, this sounds like a very good idea. My first tests worked good. Thanks!

 

[paivers]

This is interesting. I have a date dim that drives a lot of duplicated measures so could really use this. What is a "disconnected slicer", and how does a measure use it?

 

[julianwi]

take a look at this detailed Explanation: Excel extras

 

[julianwi]

and since the idea here came from Rob a link to a posting on his blog:Simplifying Time Calculations and the User Experience using Disconnected Slicers « PowerPivotPro

 

[paivers]

Good documents, thanks. I was able to get some pretty complex things working. I did change the switch function syntax a little as I found the switch( true(),... ) confusing. Here's what I did: :=switch([Slicer],
1,Calculate(Charges[Sum of ED Visits], USERELATIONSHIP(Charges[BTHMonth],factMemberMonth[Month]), FILTER(ALLSelected('factMemberMonth'),factMemberMonth[Period]="Prior")),
2,Calculate(Charges[Sum of ED Visits], USERELATIONSHIP(Charges[HCMonth],factMemberMonth[Month]), FILTER(ALLSelected('factMemberMonth'),factMemberMonth[Period]="Prior")),
blank())