Forum Discussion
Circular Dependency with Date Tables
Anonymous attached. I added two columns in PowerQuery, feel free to change as per your need.
Solution SageHi Anonymous ,
It's sorry that I can't open your shared link, could you please share it via other network storage? According to your description, it looks like that your "IsWorkDay" write based on the "IsHoliday".
Best Regards,
Teige
- parry2k
Super User
Anonymous not sure why need IsHoliday column, if you are adding that just for calculating IsWorkDay then you can get rid of IsHoliday column and add the same logic in IsWorkDay Column.
In case you need IsHoliday column then use same logic in IsWorkDay column instead of referring to IsHoliday
I need the IsHoliday column because I'm going to use that in a measure in my fact table, and I need the IsWorkDay for a calculated column in this date table. To give the full picture, my fact table has all of my data on a monthly basis, but I force the dates in that table to the first of every month so that I can still link all of my dimensions to the date table and filter on a monthly basis through that. That being the case, I use calculations to sum up the number of total days and working days (i.e., non weekends/non holidays) for measures in my fact table. I need the total days in a month and working days in a month in a column for each day so that data is captured on each row in the date table because of the fact that all my fact table dates fall on x/1/2019.
Also, I tried copying the logic of IsHoliday into IsWorkingDay to avoid the reference but I'm still getting the circular dependency.
There's probably a more elegant way to do what I'm trying to do, but the model I have has worked up to this point until I realized that I am double counting holidays that land on weekends in some of my calculations.