Help with modeling multiple date relationships

[Tihannah]

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.

[marcelsmaglhaes · Accepted solution]

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

[Tihannah]

Okay, 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?

[marcelsmaglhaes]

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

[Tihannah]

Currently, the Create Date and Business Dates are set up as dimentsion tables also. Do I create the relationship to those or to the actual fact table that contains them. I always thought I could only create one relation to the tables?

[marcelsmaglhaes]

You should have just one dimension calendar table and related this one with you fact table.

Power BI just allow one active relationship. You can create more then one, but these will be deactived to use in DAX measures. 😃

[Tihannah]

Gotcha!  Totally worked! Thank you!!!!

[marcelsmaglhaes]

Nice @Tihannah 😃

Please consider mark as solved to help other users find quickly in our community!

Regards