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

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.

Thanks MattAllington. Really helpful.

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors