Date table issue

[melanie-g]

Hi there

I have a table called Date which lists out the start and end date for each month. I added a custom column so that it lists every date. I then created a Workdays (excluding weekends and public holidays) column.

I have a Public Holidays table as well which links to my formula to work out workdays. 

I then created a DateTable to be able to calculate:

* work days (here I used the RELATED function to obtain the workdays from the table called Date. 

* work days passed

* work days remaining 

Note: Work days passed and work days remaining only work for the current month (which is OK for my model) but I know may not be the right solution. 

The issue I have now is that I have 3 States that I need to calculate working days, working days passed and working days remaining for, however, they all have different public holidays.

For example, at the moment I have a column for each state for Work Days Remaining, see below screenshot.

But only want to have only one column for Work Days Remaining, once column for Work Days Passed etc. 

So if I select QLD in my State filter, I want to see the number of work days remaining for QLD.

If I select NSW, I want to see number of work days remaining for NSW... etc. 

Any help would be very much appreciated. Please let me know if you require more information. 

Thank you.

@amitchandak 

[v-luwang-msft]

HI @melanie-g ,

Could you pls share your pbix file ,and remember to remove confidential data.

Best Regards

Lucien

[melanie-g]

Hi @v-luwang-msft 

Thanks for your response. 

Please see below link to files.

I have kept all of the date tables but removed the confidential data. I have added a "test" Dealership table and Sales table so you can see how my real model actually looked.

I want to be able to have "State" in a slicer that calculates the correct work days, work days passed and work days remaining depending on what State is selected. Each state should generate different days as they have some different public holidays.

Thanks. 

https://www.dropbox.com/scl/fo/38fdzxggsjppvkf8jf4o3/h?dl=0&rlkey=ouapt6t5k4ov3ydfcmrjqmfrd

[melanie-g]

I should probably add, the table called Date I have replicated x3 (one for each state) and there are also 3x Public Holiday tables (one for each state). I only have one "DateTable"