Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
markefrody
Post Patron
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.

 

Appreciate your kind help.

 

Best regards,

Mark V.

 

 

 

 

 

 

1 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:

a1.png

 

Best Regards

Allan

 

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

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
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.

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

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:

a1.png

 

Best Regards

Allan

 

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

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

amitchandak
Super User
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'

Greg_Deckler
Super User
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.