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
