Forum Discussion
Comparing two dates using one calendar table and USERELATIONSHIP
Hi All,
I want to compare two dates in a single measure to return a count of where delivery date > estimated delivery date within a period.
I have two tables:
1) Fact Sales ([Order ID], [Est Delivery Date ID], [Delivery Date ID])
2) Dim Date ([D Date ID], [D Date])
I can return a counts of [est delivery date] and [delivery date] each day/month/year using:
Count of estimated:CALCULATE(COUNTA([ORDER ID]),USERELATIONSHIP([EST DELIVERY DATE ID], [D DATE ID])
Count of deliveries:CALCULATE(COUNTA([ORDER ID]),USERELATIONSHIP([DELIVERY DATE ID], [D DATE ID])
However I want to now compare the two date fileds to return a count of late deliveries (EDD > DD). If I used two separate date dimensions for EDD and DD I could just use similar to:
SUMX('FACT SALES',IF( RELATED(DD_DIMENSION[DATE]) >RELATED(EDD_DIMENSION[DATE]),1,0))
How can I replicate this formula using USERELATIONSHIP?
In SQL Terms this would be equal to:
Select
Period,
Sum(Case when EDD>DD then 1 else 0 end)
From
Table
Group by
Period
Thanks!
Pbix
13 Replies
just to clarify: you state "two dates in a single measure" and then
I have two tables:
1) Fact Sales ([Order ID], [Est Delivery Date ID], [Delivery Date ID])
2) Dim Date ([D Date ID], [D Date])
..... I've underlined the terms that are confusing me....
Is there sequentiality of the Dim Date ID such that you don't need the actual date itself but simply just their comparitive relative values i.e. So that you seek a record count of Table 1 where DD > EDD ?
Hi Cahaba,
Thanks for your reply. Sorry for the confusion - yes, your interpretation is correct.
I have two tables (1 fact table, 1 calendar table date dimension).
The fact table has two date IDs ([estimate delivery date id] (int), [delivery date id] (int)) which both get their date infomtion via the calendar table dimension. (The two date ids are related to the calendar table using one active relationship [delivery date id] and one inactive relationship [estimated delivery date id]). In other examples I've read, people have used USERELATIONSHIP() to relate a single calendar table to multiple date fileds.
Without creating a calculated column in the fact table, I'd like to create a measure in the fact table that evaluates a count of orders (rows) where the [delivery date] >[estimated delivery date] for any date period.
Hope that makes more sense! :)
Pbix
Rowwise comparison of columns is very expensive at runtime. I suggest you simply add a calculated column called "Late" and add a forumula somthing like this
=if(FactTable[Del Date] > FactTable[Est Del Date],"True")
You can then use this column in your measures.
Late orders = calculate(countrows(FactTable),FactTable[Late] = "True")
