Forum Discussion

etane's avatar
etane
Helper V
3 months ago
Solved

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...
  • johnt75's avatar
    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 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.