Forum Discussion
etane
3 months agoHelper V
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, i...
- 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.
cengizhanarslan
3 months agoSuper User
Please try the logic below:
Cases per Day =
VAR _TotalCases =
SUM ( FactCases[Cases] )
VAR _ShippingDays =
SUM ( FactCases[ShippingDays] )
RETURN
DIVIDE ( _TotalCases, _ShippingDays )
VAR _TotalCases =
SUM ( FactCases[Cases] )
VAR _ShippingDays =
SUM ( FactCases[ShippingDays] )
RETURN
DIVIDE ( _TotalCases, _ShippingDays )
etane
3 months agoHelper V
Thanks. I don't see how I can sum the number of shipping days.