Forum Discussion
Help with modeling multiple date relationships
Trying to figure out how to model a dataset with several date conditions in the data.
I have -
Create Date
Business Date
Cancel Date
On Sales Count and Sales Revenue.
Normally I would just create a calendar table to join on one date, but not sure how to configure this. The business wants to see how many sales created, i.e. reserved, and how many canceled, to get to the net on Business date and be able to forecast and budget.
I have these in a filter, but they are not playing well together and I'm not sure what the metrics should be built off of for PY. If I use a Calendar Dimension table, it can only join to one of them.
Hey Tihannah
You will need to choose one date to be the mandatory in relationship (maybe create or business date). The others dates, you will create a relationship too, but they will be inactived. After that you need write your DAX measure using the function USERELATIONSHIP (USERELATIONSHIP function (DAX) - DAX | Microsoft Learn)
If this posts helps, please mark as solved to help other user find quickly in ou community!
Regards,
Marcel
7 Replies
Hey Tihannah
You will need to choose one date to be the mandatory in relationship (maybe create or business date). The others dates, you will create a relationship too, but they will be inactived. After that you need write your DAX measure using the function USERELATIONSHIP (USERELATIONSHIP function (DAX) - DAX | Microsoft Learn)
If this posts helps, please mark as solved to help other user find quickly in ou community!
Regards,
MarcelOkay, so I still use a Calender dimension table and create the relation with say "business date". I get using the USERELATIONSHIP function, but how would I create a relationship with the others?
You can create in the same way that you create the relation with business date. Just drag and drop the dates. They relationships will create and they will be unactived automatically and then you can use the DAX function.
Regards,
Marcel
