Dates between dimensions and complete Dates

[Anonymous]

Hi everybody !

I have the following problem:

I have 2 dimensions (Date and Branch) and a table of facts (Sales).
The Branch dimension is related to my table of facts through the Branch_ID, in addition this table contains "Open Date" and "Closing Date", not all rows have the full date, there are null fields in "open dates" and in many more in "Closing Date" since this information is only for closed branches.

On the other hand, my table of facts is related through a "date" field with my date dimension.

What I need to know is how many branches opened and closed in a given month using the date dimension filter.
I had planned to pass the 2 dates (Open and Closed) to my fact table and then create measurements using USERRELATIONSHIP, but I didn't know what to do with all the rows in null.

What do you recommend I do? How would you solve this problem?

Thanks!
Regards !!

[amitchandak · Accepted solution]

Join the date dimension with both the dates. And then use userelation to choose one. When you using the close date. Add filter to calculate formula to have not(isblank(cloase_date)) filter 

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

How about creating a bridge table to store expand date based on its id? It will help to mapping your records so that you can use bridge table date field to filter on date range based on two date fields and correspond records.

Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

Join the date dimension with both the dates. And then use userelation to choose one. When you using the close date. Add filter to calculate formula to have not(isblank(cloase_date)) filter