Forum Discussion
Cases per Day Calculation
Hello.
I am trying to divide total cases by shipping days and display it on a matrix table with year and month in the column.
I can't figure out how to get the cases per day to show. So, in the year total, I should get total cases for that year divided by total shipping days in that year. Conversely, in each month, I should get total cases for that month divided by total shipping days in that month.
For example, in January 2026, there were 70 cases and 21 shipping days. So, cases per day should be 70/21 = 3.33. And, for 2025, 698/253 = 2.75
However, I can't come up with a dax that does this calculation. Help.
Sample file here: Link
Thanks.
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.
4 Replies
Please try the logic below:
Cases per Day =
VAR _TotalCases =
SUM ( FactCases[Cases] )
VAR _ShippingDays =
SUM ( FactCases[ShippingDays] )
RETURN
DIVIDE ( _TotalCases, _ShippingDays )Thanks. I don't see how I can sum the number of shipping days.
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.
Thank you! I love how easy it is to apply your solution.
