topic Re: Cases per Day Calculation in DAX Commands and Tips

Cases per Day Calculation

etane — Fri, 15 May 2026 03:27:47 GMT

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.

Re: Cases per Day Calculation

cengizhanarslan — Fri, 15 May 2026 06:40:39 GMT

Please try the logic below:

Cases per Day =
VAR _TotalCases =
SUM ( FactCases[Cases] )
VAR _ShippingDays =
SUM ( FactCases[ShippingDays] )
RETURN
DIVIDE ( _TotalCases, _ShippingDays )

Re: Cases per Day Calculation

johnt75 — Fri, 15 May 2026 08:21:44 GMT

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.

Re: Cases per Day Calculation

etane — Fri, 15 May 2026 15:18:53 GMT

Thank you! I love how easy it is to apply your solution.

Re: Cases per Day Calculation

etane — Fri, 15 May 2026 15:20:15 GMT

Thanks. I don't see how I can sum the number of shipping days.