Forum Discussion
Cases per Day Calculation
- 3 months ago
Add a new column onto your calendar table to flag whether a day is a holiday or not. You can change the existing ADDCOLUMNS to be
Calendar = VAR Days = CALENDAR(DATE(year(TODAY())-7,1,1),DATE(year(today())+1,12,31)) VAR Holidays = { (1, 1), (2, 19), (5, 27), (7, 4), (9, 2), (11, 28), (11, 29), (12, 24), (12, 25) } Return ADDCOLUMNS( Days, "Today", TODAY(), "Year#", YEAR([Date]), "Year", FORMAT(YEAR([Date]),"#"), "Month#", VALUE(FORMAT(MONTH([Date]),"#")), "Month Number",FORMAT([Date],"MM"), "Month", FORMAT([Date],"mmm"), "Quarter#",QUARTER([Date]), "Quarter","Q"&FORMAT([Date],"Q"), "Year Month", FORMAT([Date],"YYYYMM"), "Year Quarter", FORMAT(YEAR([Date]),"#") &"Q"&FORMAT([Date],"Q"), "Week Number", FORMAT(WEEKNUM([Date]),"00"), "Week In Month",FORMAT(WEEKNUM([Date],2) - WEEKNUM(EOMONTH([Date],-1)+1,2)+1,"#"), "Is Holiday", ( MONTH( [Date] ), DAY( [Date] ) ) IN Holidays )You can then change the [Shipping Days] measure to be
Shipping Days = VAR MinDate = MIN( Calendar[Date] ) VAR MaxDate = MAX( Calendar[Date] ) VAR Holidays = CALCULATETABLE( VALUES( Calendar[Date]), Calendar[Is Holiday] = TRUE ) VAR WorkingDays = NETWORKDAYS( MinDate, MaxDate, 1, Holidays) RETURN WorkingDaysAnd the [Case per Ship Day] stays the same.
You should also change the relationship between Calendar and the fact table to be single direction, there's no need for it to be bi-directional.
See the attached PBIX for a working sample.
Add a new column onto your calendar table to flag whether a day is a holiday or not. You can change the existing ADDCOLUMNS to be
Calendar =
VAR Days = CALENDAR(DATE(year(TODAY())-7,1,1),DATE(year(today())+1,12,31))
VAR Holidays = {
(1, 1),
(2, 19),
(5, 27),
(7, 4),
(9, 2),
(11, 28),
(11, 29),
(12, 24),
(12, 25)
}
Return ADDCOLUMNS(
Days,
"Today", TODAY(),
"Year#", YEAR([Date]),
"Year", FORMAT(YEAR([Date]),"#"),
"Month#", VALUE(FORMAT(MONTH([Date]),"#")),
"Month Number",FORMAT([Date],"MM"),
"Month", FORMAT([Date],"mmm"),
"Quarter#",QUARTER([Date]),
"Quarter","Q"&FORMAT([Date],"Q"),
"Year Month", FORMAT([Date],"YYYYMM"),
"Year Quarter", FORMAT(YEAR([Date]),"#") &"Q"&FORMAT([Date],"Q"),
"Week Number", FORMAT(WEEKNUM([Date]),"00"),
"Week In Month",FORMAT(WEEKNUM([Date],2) - WEEKNUM(EOMONTH([Date],-1)+1,2)+1,"#"),
"Is Holiday", ( MONTH( [Date] ), DAY( [Date] ) ) IN Holidays
)
You can then change the [Shipping Days] measure to be
Shipping Days = VAR MinDate = MIN( Calendar[Date] )
VAR MaxDate = MAX( Calendar[Date] )
VAR Holidays = CALCULATETABLE(
VALUES( Calendar[Date]),
Calendar[Is Holiday] = TRUE
)
VAR WorkingDays = NETWORKDAYS( MinDate, MaxDate, 1, Holidays)
RETURN WorkingDays
And the [Case per Ship Day] stays the same.
You should also change the relationship between Calendar and the fact table to be single direction, there's no need for it to be bi-directional.
See the attached PBIX for a working sample.
Thank you! I love how easy it is to apply your solution.