cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RJ Regular Visitor
Regular Visitor

Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

The master calendar is created as per this thread

 

http://community.powerbi.com/t5/Desktop/Possible-Tip-Trick-Dynamic-Date-Dimension-Table/m-p/8795#M15...

 

But sometimes there are two dates like order date and invoice date

 

I wanted to do a report like this Where one column was based on the date of order and one on the date of invoice

 

Grapph.GIF

 

This is covered under this blog post

 

http://sqldusty.com/2015/10/21/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-2/

 

use relationship

 

So in effect one calendar is set up as so

 

The date in the calendar is linked to the Invoice Date in this example

A unlinked join is created for the Order Date

 

Grapph.GIF

 

So the calendar date is linked to the Invoice Date

and then the Order Date is linked to the calandar date as follows

 

Sales£OrdDate = CALCULATE([Sales£M] , USERELATIONSHIP(Header[Order Date] , CalendarInv[Date]))

9 REPLIES 9
alexchseng Frequent Visitor
Frequent Visitor

Re: Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

Hi @RJ, I have tried the instructions provided, however for some weird reason, I get a group of total at the top without dates. This is despite each of my rows in the Application table has dates. 

 

Here is how I have linked my DateTable, where the active link is between Date and Application Received Date. The inactive date is between Date and Application Offer Sent Date. With the cross filter direction, I have tried both "Single" and "Both", but to no avail.

 

Capture1.jpg 

 

Here is the output that I am getting. Noticed that next to 6, there isn't any Year or Month information.

 

Capture.JPG

 

 

Any ideas? Any assistance would be much appreciated. Thanks.

Super User
Super User

Re: Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

Have you made absolutely sure that all of the dates that show up in the Application table are covered by the range in the Date table?

alexchseng Frequent Visitor
Frequent Visitor

Re: Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

Hi @KHorseman, thanks for the hint. I found out that those 6 anamolies were due to the time stamps. Because I have used DateTable = CALENDAR(MINX('Application','Application'[Application Received Date]), TODAY()) to generate the Date table, the default timestamp is 12:00:00 AM. Hence, any row in the Offer Sent Date without 12am could not be matched.

 

Is there a way to force USERELATIONSHIP() to only match on the date portion and not the time?

 

Capture.JPG

Super User
Super User

Re: Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

I don't think so. The relationship exists on the tables themselves, not the particular measure being executed on them. I recommend separating date and time into separate columns. If you need a full date/timestamp then perhaps just make a custom column containing only the date from that, and use that to create the relationship.

alexchseng Frequent Visitor
Frequent Visitor

Re: Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

Thanks for the suggestions. I have created a custom column containing only the date part, and am using that to create the relationship. Everything is working as expected now. 

BKnecht Regular Visitor
Regular Visitor

Re: Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

I have been having the same exact problem, but the changing of the Date columns to "Date" instead of "Date/Time" didn't work (after I read this post I was confident that would fix it) - I'm getting a (BLANK) when I create the measure I'm looking for. 

 

I have an inactive relationship between the columns, and my measure looks like: 

 

New Oppys Created = CALCULATE(SUM(Opportunity[Annual_NCVI__c]),Opportunity[New_Renew__c]="New",USERELATIONSHIP(Opportunity[CreatedDate],'Date'[Date]))

 

Date.png

 

Any ideas? This is driving me nuts and I can't figure out what's wrong, everything looks like it should to me. 

Super User
Super User

Re: Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

@BKnecht Does it help if you change that middle argument to a full FILTER statement?

 

New Oppys Created = CALCULATE(

SUM(Opportunity[Annual_NCVI__c]),

FILTER(Opportunity, Opportunity[New_Renew__c]="New"),

USERELATIONSHIP(Opportunity[CreatedDate],'Date'[Date])

)

 

Also as previously suggested make sure that your date table's range actually covers all possibilities in the Opportunity table.

BKnecht Regular Visitor
Regular Visitor

Re: Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

For whatever reason the FILTER statement doesn't give me what I want. But, I found I had Date Ranges that weren't accounted for in some of the other relationships connected to the date table - even if the relationship I was using had the ranges covered for, some of the other relationships didn't - and it looks like that's what was breaking it.

 

Thanks a lot for the quick response!

Super User
Super User

Re: Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

Yeah when you have multiple connections to the same date dimension you can get some odd misbehaviors that are very hard to track down. Especially when you're using inactive relationships that are toggled by USERELATIONSHIP and you have to compare two or more different relationships in one measure. *glares angrily at another window*