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

[RJ]

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

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

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]))

[alexchseng]

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.

 

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

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

[KHorseman]

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]

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?

[KHorseman]

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]

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]

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]))

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

[KHorseman]

@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]

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!

[KHorseman]

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*