Forum Discussion
Comparing two dates using one calendar table and USERELATIONSHIP
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.
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/
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.
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.