Counting last N days with data, excluding weekends

[Anonymous]

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" column returns 1 if we have data for the day and 0 if not. I am counting the number of days with data available for the past N days (selected by a parameter called [Number of Days Value] ) and then dividing it by the number of days considered. So for example, if I select 3 with the parameter, I am looking at the actual date and 2 days before that, if I have 1, 1, 0 in "Data Availability" then the ratio is 2/3 = 66.67% and this works well, but I would like to ignore weekends, so if May 30 and 31 are weekend days, then I want June 1 to take data for May 28, 29 and June 1 (skipping May 30, 31).

So June 1, 2, 3 should not be 80%, but 100%, as the weekdays had data and I'd need to ignore the weekend days. I tried to set up a DateType field in My calendar that has 2 values "Weekday" and "Weekend" and then use it to filter my calendar table, but nothing worked.

Secondly, I also want to show nothing, if there are not enough days available, based on the parameter specified, so for example, if I select 7 and I am only looking at the last 1 month of data (May 24 - June 23), then on May 26 I won't have enough data points and I want to show blank.

The measure and a picture of the table can be seen below. I really appreciate any help, as I am out of ideas at this stage.

Data Availability Ratio =

VAR num_of_selected_days = [Number of Days Value]

VAR date1 =

LASTDATE ( 'Calendar'[Date] )

VAR date2 = date1 - num_of_selected_days

VAR num_of_days_with_data =

CALCULATE (

SUM ( Futures[Data Availability] ),

FILTER (

ALL ( 'Calendar'[Date] ),

'Calendar'[Date] > date2

&& 'Calendar'[Date] <= date1

)

)

Var Ratio = DIVIDE(num_of_days_with_data, num_of_selected_days)

Return

CALCULATE(IF(ISBLANK( ratio ), 0, Ratio ), FILTER('Calendar', 'Calendar'[DayType] = "Weekday" ))

[Icey · Accepted solution]

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]

@Icey thanks a lot, very clever. Can you provide a short textual summary of the logic, please? I just want to fully understand how you were thinking here, as even though I can understand the portions, I don't get the full picture & would like to understand it completely.

[Icey]

Hi @Anonymous ,

Please check the comments in the expression. Hope I explained clearly. If not, please let me know.

Data Availability Ratio = 
VAR num_of_selected_days = [Number of Days Value Value]
VAR date1 =
    LASTDATE ( 'Calendar'[Date] )  ---------------Get current date of this row.
VAR date2 = date1 - num_of_selected_days   ---------Previous n days of current date.
VAR WeekendDays =
    CALCULATE (
        COUNT ( 'Calendar'[DayType] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[DayType] = "Weekend"
                && 'Calendar'[Date] > date2
                && 'Calendar'[Date] <= date1
        )
    )      -----------------------------------------Count weekends between date1 and date2.
VAR num_ =
    IF ( MOD ( WeekendDays, 2 ) = 0, WeekendDays , WeekendDays+ 1 ) -------If weekends count is  a multiple of 2，return the count, else return count+1. Because Saturday and Sunday are always together.
VAR date3 =
    IF ( WeekendDays >= 1, date2 - num_, date2 )  ---------Exclude weekends.
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" )
        )
    )

Best Regards,

Icey

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

[amitchandak]

@Anonymous , refer to this file, It has an example of working days between dates.

Check 2nd Page

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[Anonymous]

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]))