cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Patron

## Count Past and Future Days in Current Month Except Sundays

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.

Best regards,

Mark V.

1 ACCEPTED SOLUTION
Community Support

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.

6 REPLIES 6
Community Support

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.

Post Patron

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

Community Support

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.

Post Patron

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

Super User

@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'

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors