cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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 13
Memorable Member
Memorable Member

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   ?

www.CahabaData.com

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks MattAllington. Really helpful.

Hi Matt, 

 

Thanks for your reply 🙂

 

Yes, was thinking about adding a helper column to do this (I'd previously read that calc columns are also quite expensive) so this might be the route to go down. 

 

Is it possible at all to do it via a measure? I'm looking to learn how to compare different date values from different tables as I suspect I'll have to do this alot - without always needing to generate a calculated column. Is it possible to compare two actual dates in the related calendar table, where the dates both reference the same calendar table via two individual relationships?? 

 

Thanks!

 

Pbix

Calculated colums can be expensive but only if it has a high cardinality.  You can read my guide here

http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

Read the section under general rules when calc columns are OK.

 

Yes you can do this via a measure, but as I mentioned above, rowwise comparison is very expensive.  Power Pivot has 2 engines, a Storage Engine (SE) and a Formula Engine (FE).  SE is fast, compressed, multi threaded and cached.  FE is slow, can't use compressed data, single threaded and not cached.  Rowwise comparisons like you mention will be forced to use the formula engine which is not ideal.  However if you have a small data set and you are happy with the peformance then you can do it in a measure.  But why do you want to?

 

Also, why do you want to use your calendar table to compare the dates?  The data is in the fact table so there is no need to reference the calendar table.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi @MattAllington

 

I have a similar situation whereby I need to compare the dates in my fact table with a date table. For example, my fact table is named Bookings and has three columns: BookingID, Check In Date, and Check Out Date. I have a generic DateTable that has a only 1 column (Date) consisting of a sequential date range from min(check in date) to max(check out date).

 

What I am trying to achieve is, for each Date in my generic DateTable, I would like to know how many people are in room, ie. the number of rows in the Bookings table, filtered by Bookings[Check In Date] < DateTable[Date] AND Bookings[Check Out Date] > DateTable[Date]. In terms of visualisation, I am trying to create a simple table with 2 columns, the date range on the left, and the number of people in room per day on the right.

 

Do you have any suggestions on the best way forward? I look forward to hear from you soon.

Sorry for my slow reply.  Have you solved this?  It seems to me that your problem is similar to this one that I wrote about recently.  

 

https://powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi @MattAllington, thanks a lot for sharing your article. I did try your calculate formula, but it seems that I am not being returned the correct results. What I did was to first create a column known as Number of Days where the value is just 1 for every row. This is to mirror your Calendar[Working Day] column. 

 

When I performed the following calculation:

Number In Room = CALCULATE(SUM(DateTable[Number of Days]),FILTER(DateTable,DateTable[Date] < MAX(Booking[Booking Check Out Date])))

all I had was 933 as the value for every row in my Number In Room column. This 933 actually refers to the number of rows in my DateTable.

 

Capture.jpg

It seems to me that instead of going through row by row in the Bookings table to retrieve the check out date, the above formula had taken the max date in my check out date column and apply it across the whole DateTable. Is my understanding correct?

 

Do you have any suggestions on how this could be fixed? Thanks.

@alexchseng I am still here - just busy :-).

 

You have posted your DAX formula and it seems to be a calcualted column, but there is a big issue.  Every DAX formula can give a different result depending on the context.    It is not clear which table your calculated column is in, if there are other tables in your model, if there are other tables it is not clear how they are joined.  All these things make a difference and it is impossible to help without full knowledge of all of these things.

 

Can you post a simple working copy of your model with non-senstive test data?  Ideally you should also show what you are trying to achieve, maybe using Excel to illustrate.

 

You can post workbooks with dropbox or similar.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi @MattAllington, thanks for your reply. I have managed to resolve the issue by filtering on the Booking table, as opposed to the DateTable. This also allows me to simply count the rows in my Booking table, as opposed to summing the Number of Days column.

 

New formula is as follows: 

Number In Room = CALCULATE(COUNTROWS(Booking),FILTER(Booking,DateTable[Date] >= Booking[Booking Check In Date] && DateTable[Date] < Booking[Booking Check Out Date]))

 

Thanks a lot for all your assistance. 

part of my question was whether that Date ID was sequential.

 

if so - then you don't even have to join to the Dim Date table - you can just compare the relative values in Table 1.

www.CahabaData.com

Hi @CahabaData

 

Thanks - yes - date ids are sequential so this is something I considered doing. Just two small issues for me - 

 

1) Where the EDD/DD is unknown then the dateid = -1 (so would need some row evaluation to check if dateid <>-1

2) to do similar (But with the actual dates) I tried returning, for each row, the actual date value for each dateid using something along the lines of:

 

CALCULATE(VALUES(Dim Date[Date]), USERELATIONSHIP(fact[dateid],Dim Date[dateid])

 

But, for a reason I haven't yet worked out, the above formula didn't always return the correct EDD/DD date for each dateid - does this formula look correct?

 

Also - just learning how to relate fields together so v keen to learn about doing this via a measure!

 

Thanks 🙂

 

Pbix

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors