Count Past and Future Days in Current Month Except Sundays

[markefrody]

Hi all,

I am trying the below DAX codes to compute for the following but it does not compute the days properly. Not sure if I need measures or calculated columns for these or if the codes are correct:

1.) Counting the past number of days in the current month excluding today and Sundays

Days Before Today = CALCULATE(
    COUNTROWS('Date'),FILTER(
        'Date','Date'[Date]<TODAY() && MONTH('Date'[Date])=MONTH(TODAY()) && WEEKDAY('Date'[Date])<>7)
        )

2.) Count the remaining days of the current month excluding today, Sundays, and past days

Days After Today = CALCULATE(
    COUNTROWS('Date'),FILTER(
        'Date','Date'[Date]>TODAY() && MONTH('Date'[Date])=MONTH(TODAY()) && WEEKDAY('Date'[Date])<>7)
        )

You can find the data I am using (here) . The date I am basing the number of days is the column "StatusDateEnd" (highlighted in yellow). So for example the date in "StatusDateEnd" is showing "5/15/2020". So the days before excluding today (example today is 5/15/2020) and Sundays should be 12 days. While the days after excluding today (5/15/2020) and Sundays should be 13 days.

Appreciate your kind help.

Best regards,

Mark V.

[v-alq-msft]

Hi, @markefrody 

You may create a Date table as below.

Calendar = CALENDARAUTO()

Then you could create two measures as follows.

Days Before Today = 
var _date = Sheet1[StatusDateEnd]
return
CALCULATE(
    DISTINCTCOUNT('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date]<_date&&
        'Calendar'[Date]>=DATE(YEAR(_date),MONTH(_date),1)&&
        WEEKDAY('Calendar'[Date])<>1
    )
)

Days After Today = 
var _date = Sheet1[StatusDateEnd]
return
CALCULATE(
    DISTINCTCOUNT('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date]>_date&&
        'Calendar'[Date]<=EOMONTH(_date,0)&&
        WEEKDAY('Calendar'[Date])<>1
    )
)

Best Regards

Allan

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

[markefrody]

Thanks @v-alq-msft  Allan. Is there anyway to exclude Sundays in the measure?

[v-alq-msft · Accepted solution]

Hi, @markefrody 

You may try the following calculated columns.

Days Before Today = 
var _date = Sheet1[StatusDateEnd]
return
CALCULATE(
    DISTINCTCOUNT('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date]<DATE(YEAR(_date),MONTH(_date),DAY(_date))&&
        'Calendar'[Date]>=DATE(YEAR(_date),MONTH(_date),1)&&
        WEEKDAY('Calendar'[Date])<>1
    )
)

Days After Today = 
var _date = Sheet1[StatusDateEnd]
return
CALCULATE(
    DISTINCTCOUNT('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date]>DATE(YEAR(_date),MONTH(_date),DAY(_date))&&
        'Calendar'[Date]<=EOMONTH(_date,0)&&
        WEEKDAY('Calendar'[Date])<>1
    )
)

Result:

Best Regards

Allan

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

[markefrody]

Thanks Allan @v-alq-msft ! Working perfectly!

[amitchandak]

@markefrody , Try line

Days Before Today = CALCULATE(
    COUNTROWS('Date'),FILTER(
        all('Date'),'Date'[Date]<TODAY() && ('Date'[Date])>=(EOMONTH(TODAY(),-1)+1) && WEEKDAY('Date'[Date],2)<>7)
        )

Days After Today = CALCULATE(
    COUNTROWS('Date'),FILTER(
        all('Date'),'Date'[Date]>TODAY() && ('Date'[Date])<=EOMONTH(TODAY(),0) && WEEKDAY('Date'[Date],2)<>7)
        )

I have used all('Date') , you can experiment with allselected('Date')  or 'Date'

[Greg_Deckler]

Take a look at Net Work Days, should just have to modify it slightly to include Saturdays. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109