Date Table and Multiple Systems

[Anonymous]

Hi 

How does one incorporate into a model a date table with multiple companies (systemid) that have different fiscal month months and/or years given the date table needs to have unique date column?

[v-kelly-msft]

Hi @Anonymous ,

What is your model like?Can you provide some sample data with expected output?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Kelly

Sorry I dont have a file, it is more of a generic question.

The FACT table would have a column for systemID and dateID (linking to DIM Date/Calendar table). The problem is the date table has to have a unique date, but that date could belong to different financial years for the two different systemid's..

@amitchandak 

[Anonymous]

@v-kelly-msft 
@amitchandak 

could you perhaps assist?

[PaulDBrown]

You need to add columns to the date table specifying the fiscal month and fiscal year using SWITCH expressions determining the values for the range of dates within the fiscal periods. Make sense?

[Anonymous]

Hi Paul

Thank you for your prompt response.

Would you mind expanding please? Perhaps add something to the PBIX file if you dont mind.

[PaulDBrown]

Sorry, what PBIX file?

[Anonymous]

I may need your email address to share the link succesfully with you.

Alternative, how can I share it here?

[Anonymous]

Apologies.

Here it is. 

https://drive.google.com/file/d/1Zjl4kEvFjJ1NSHbZs_3cYX58EsMRdsTP/view?usp=sharing

[PaulDBrown]

Can you make the link downloadable? It is requesting a sign-in

[Anonymous]

https://drive.google.com/uc?id=1hdof4HSyGYs2qMuSkcNXlkSjAw4sfjyl&export=download