Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Counting last N days with data, excluding weekends

Dear Forum Members,   I have a nicely working DAX code with a parameter (N number of days to consider), but I cannot get it to ignore weekend days (Sat, Sun).   Basically, the "Data Availability"...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Try this:

    Data Availability Ratio = 
VAR num_of_selected_days = [Number of Days Value Value]
VAR date1 =
    LASTDATE ( 'Calendar'[Date] )
VAR date2 = date1 - num_of_selected_days
VAR WeekendDays =
    CALCULATE (
        COUNT ( 'Calendar'[DayType] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[DayType] = "Weekend"
                && 'Calendar'[Date] > date2
                && 'Calendar'[Date] <= date1
        )
    )
VAR num_ =
    IF ( MOD ( WeekendDays, 2 ) = 0, WeekendDays , WeekendDays+ 1 )
VAR date3 =
    IF ( WeekendDays >= 1, date2 - num_, date2 )
VAR num_of_days_with_data =
    CALCULATE (
        SUM ( Futures[Data Availability] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] > date3
                && 'Calendar'[Date] <= date1
                && 'Calendar'[DayType] = "Weekday"
        )
    )
VAR Ratio =
    DIVIDE ( num_of_days_with_data, num_of_selected_days )
RETURN
    IF (
        MAX ( 'Calendar'[DayType] ) = "Weekday",
        CALCULATE (
            IF ( ISBLANK ( ratio ), 0, Ratio ),
            FILTER ( 'Calendar', 'Calendar'[DayType] = "Weekday" )
        )
    )

     

    BTW, .pbix file attached.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I think this is the count of workdays in the measure below, but in my scenario I'm trying to count a different column that has 0 or 1 (if we have data for the day 1, if not 0) and as an addition I only want to take workdays into account, so if my parameter is 3 (looking at 3 last days) and I am looking at a row, where the date is a Monday, it should consider the Monday and then the Thursday & Friday before that, ignoring the 2 weekend days.

 

Working Days = CALCULATE(Sum('Date'[Working day]),VALUES('Order Dim'[Order No]),filter(all('Date'),'Date'[Date]>=[Min Order Date OD] && 'Date'[Date] <=[Max Deilvery OD]))