Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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], ...
Need Help
MattAllington's avatar
MattAllington
Community Champion
9 years ago

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.

alexchseng's avatar
alexchseng
Regular Visitor
9 years ago

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.