Finding daily value based on work date between 2 dates in power query

Question

Hi, I apologize in advance for the verbose question.&nbsp;I have a table with Start_Date and End_Date and values for 4 services. I need to create a list to obtain daily values based on work dates to include holidays.&nbsp;I created a calculated table in powerbi that works perfectly, but i am not able to merge the information in power query with other existing tables so i am trying to create in power query. The code i used to create the calculated table (and the non-working day column in my powerbi date table) is below. I have created a list in power query of the dates between the start and stop date, but i need to remove the dates from the calculation that are not work dates (sat, sun, holiday) preferably built into the code, not having to create a separate holiday table.__________________________________________TBL_EST_FT_PRODUCTION = SELECTCOLUMNS(&nbsp; &nbsp; GENERATE(&nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TBL_DA_Poly_Production_Rates,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Daily_OH_Est_Prod", DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Tbl_Da_Poly_Production_rates[OHTotal], &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LOOKUPVALUE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'TBL_DA_Poly_Production_Rates'[DA_EXPECTED_END_DATE],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Non-Working Days],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -RELATED('Date'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Daily_UG_Est_Prod", DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TBL_DA_Poly_Production_Rates[UGTotal], &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LOOKUPVALUE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'TBL_DA_Poly_Production_Rates'[DA_EXPECTED_END_DATE],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Non-Working Days],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -RELATED('Date'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Daily_FiberSpliced_Est_Prod", DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TBL_DA_Poly_Production_Rates[FibersSplicedTotal], &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LOOKUPVALUE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'TBL_DA_Poly_Production_Rates'[DA_EXPECTED_END_DATE],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Non-Working Days],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -RELATED('Date'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Daily_Units_Est_Prod", DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TBL_DA_Poly_Production_Rates[UnitTotal], &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LOOKUPVALUE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'TBL_DA_Poly_Production_Rates'[DA_EXPECTED_END_DATE],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Non-Working Days],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -RELATED('Date'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALLNOBLANKROW('Date'),&nbsp; &nbsp; &nbsp; &nbsp; AND(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date] &gt;= TBL_DA_Poly_Production_Rates[DA_EXPECTED_START_DATE],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date'[Date] &lt; TBL_DA_Poly_Production_Rates[DA_EXPECTED_END_DATE]&nbsp; &nbsp; &nbsp; &nbsp; ) &nbsp; &nbsp; )),&nbsp; &nbsp; "EST_PRODUCTION_Date", 'Date'[Date],&nbsp; &nbsp; "DA_Poly_ID",TBL_DA_Poly_Production_Rates[DAPolyID],&nbsp; &nbsp; "Production_Unit_ID",TBL_DA_Poly_Production_Rates[ProductionUnitID],&nbsp; &nbsp; "Production_Unit",TBL_DA_Poly_Production_Rates[ProductionUnit],&nbsp; &nbsp; "DA_OH_EST_PROD", [Daily_OH_Est_Prod],&nbsp; &nbsp; "DA_UG_EST_PROD", [Daily_UG_Est_Prod],&nbsp; &nbsp; "DA_UNITS_EST_PROD", [Daily_Units_Est_Prod],&nbsp; &nbsp; "DA_FIBERSSPLICED_EST_PROD", [Daily_FiberSpliced_Est_Prod])_________________________________Non-Working Days = &nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; TRUE(),&nbsp; &nbsp; -- January - Saturdays, non-working days &nbsp; &nbsp; &nbsp; &nbsp; //// Non Working Day in January&nbsp; &nbsp; MONTH([Date]) = 1 &amp;&amp; WEEKDAY([Date], 1 ) = 7, "Non-Working Day",&nbsp; &nbsp; -- February- Saturdays, non-working days &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//// Non Working Day in February&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 2 &amp;&amp; WEEKDAY([Date], 1 ) = 7, "Non-Working Day",&nbsp; &nbsp; -- March- Saturdays, non-working days &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//// Non Working Day in March&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 3 &amp;&amp; WEEKDAY([Date], 1 ) = 7, "Non-Working Day",&nbsp; &nbsp; -- Sundays&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//// Sunday&nbsp; &nbsp; &nbsp; &nbsp; WEEKDAY([Date], 1 ) = 1, "Non-Working Day",&nbsp;&nbsp;-- Thanksgiving&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 11 &amp;&amp; DAY([Date]) IN { 22, 23, 24, 25, 26, 27, 28 } &amp;&amp; WEEKDAY([Date], 1 ) = 5, "Thanksgiving",&nbsp; &nbsp; &nbsp; &nbsp; -- Friday after Thanksgiving&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 11 &amp;&amp; DAY([Date]) IN { 23, 24, 25, 26, 27, 28, 29 } &amp;&amp; WEEKDAY([Date], 1 ) = 6, "Friday after Thanksgiving",&nbsp; &nbsp; -- Christmas Eve (including weekend observations)&nbsp; &nbsp; &nbsp; &nbsp; //// When falling on Saturday, move to preceding Friday&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 12 &amp;&amp; DAY([Date]) = 23 &amp;&amp; WEEKDAY([Date], 1 ) = 6, "Christmas Eve (Observed)",&nbsp; &nbsp; &nbsp; &nbsp; //// When falling on Friday, move to preceding Thursday&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 12 &amp;&amp; DAY([Date]) = 23 &amp;&amp; WEEKDAY([Date], 1 ) = 5, "Christmas Eve (Observed)",&nbsp; &nbsp; &nbsp; &nbsp; //// When falling on Sunday, move to following Monday&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 12 &amp;&amp; DAY([Date]) = 25 &amp;&amp; WEEKDAY([Date], 1 ) = 2, "Christmas Eve (Observed)",&nbsp; &nbsp; &nbsp; &nbsp; //// When falling on normal workday (except Friday), do not shift holiday observation&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 12 &amp;&amp; DAY([Date]) = 24 &amp;&amp; WEEKDAY([Date], 1 ) IN { 2, 3, 4, 5 }, "Christmas Eve",&nbsp; &nbsp; &nbsp;-- Christmas Day (including weekend observations)&nbsp; &nbsp; &nbsp; &nbsp; //// When falling on Saturday, move to preceding Friday&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 12 &amp;&amp; DAY([Date]) = 24 &amp;&amp; WEEKDAY([Date], 1 ) = 6, "Christmas Day (Observed)",&nbsp; &nbsp; &nbsp; &nbsp; //// When falling on Sunday, move to following Monday&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 12 &amp;&amp; DAY([Date]) = 26 &amp;&amp; WEEKDAY( [Date], 1 ) = 2, "Christmas Day (Observed)",&nbsp; &nbsp; &nbsp; &nbsp; //// When falling on Monday, move to following Tuesday&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 12 &amp;&amp; DAY([Date]) = 26 &amp;&amp; WEEKDAY([Date], 1 ) = 3, "Christmas Day (Observed)",&nbsp; &nbsp; &nbsp; &nbsp; //// When falling on normal workday (except Monday), do not shift holiday observation&nbsp; &nbsp; &nbsp; &nbsp; MONTH([Date]) = 12 &amp;&amp; DAY([Date]) = 25 &amp;&amp; WEEKDAY([Date], 1 ) IN { 3, 4, 5, 6 }, "Christmas Day",&nbsp; &nbsp; &nbsp; &nbsp; -- Leave working days blank&nbsp; &nbsp; &nbsp; &nbsp; BLANK())

scncks1 · Accepted Answer

my solution, but if anything can improve, please let me know:

I created a networkdays function then created a list to expand the dates, it covers the weekends, but not the holidays. I used the same function to determine the network days to determine the daily values -->

______________________________________
Function -->

= (StartDate as date, EndDate as date) as number =>

let
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
CountDays = List.Count(RemoveWeekends)

in
CountDays

_________________________________

= Table.AddColumn(#"Changed Type1", "Dates_Test", each let
AllDates = {Number.From([DA_EXPECTED_START_DATE])..Number.From([DA_EXPECTED_END_DATE])},

StartDate =
List.Transform(AllDates, each Date.From(_)) in StartDate)

Forum Discussion

Finding daily value based on work date between 2 dates in power query

1 Reply

Related content

Storage increasing daily

SQDCP Daily Dashboard

Monthly sales budget data allocation to daily grain

tool to find measures relationship

Daily Sum