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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amulder
Helper I
Helper I

How to count number of Mondays within a date range?

I've been searching and searching but I can't seem to find the syntax for a measure that will allow me to count the number of Mondays within a date range.  The date range will be selected through a slicer on the same page.

 

What is the DAX syntax in order to do this?

 

Thanks!

1 ACCEPTED SOLUTION

Hi @amulder,

 

 

Maybe this one? Please share a dummy sample file.

Measure =
CALCULATE ( COUNT ( 'Date'[DOW), all('table'[HourBlock] ) )

Best Regards,

Dale

Community Support Team _ Dale
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

9 REPLIES 9
PrizzC15
Frequent Visitor

Hi guys,

 

I would like to get solution as below in Power BI :

PrizzC15_0-1667373791530.png

Zubair_Muhammad
Community Champion
Community Champion

@amulder

 

You can use this MEASURE

 

Measure =
VAR selected_dates =
    ADDCOLUMNS (
        GENERATESERIES ( MIN ( Table1[Date] ), MAX ( Table1[Date] ) ),
        "Weekday", WEEKDAY ( [Value], 3 )
    )
RETURN
    COUNTROWS ( FILTER ( selected_dates, [Weekday] = 0 ) )

Regards
Zubair

Please try my custom visuals

@amulder

 

If you want to know which dates are Monday

You can use this MEASURE

 

Measure 2 =
VAR selected_dates =
    ADDCOLUMNS (
        GENERATESERIES ( MIN ( Table1[Date] ), MAX ( Table1[Date] ) ),
        "Weekday", WEEKDAY ( [Value], 3 )
    )
RETURN
    CONCATENATEX (
        FILTER ( selected_dates, [Weekday] = 0 ),
        [Value],
        UNICHAR ( 10 )
    )

Regards
Zubair

Please try my custom visuals

@amulder

 

Please see file attached as well

 

7324573.png


Regards
Zubair

Please try my custom visuals

I need the day of the week to adjust automatically when I add it to my matrix but only be filtered by the column not the row.  Right now I'm just using a simple COUNT('Date'[DOW) which works on its own, however in my matrix I have a column titled HourBlock that breaks my count of records (Number) down by hour.  Since not every hour within the month filter has records the DOW_COUNT gets filtered accordingly.  I don't want it to.  I essentially want the values that I see in the small matrix beside the big one which has the official counts of the days of the week.

 

Untitled.jpg

Hi @amulder,

 

 

Maybe this one? Please share a dummy sample file.

Measure =
CALCULATE ( COUNT ( 'Date'[DOW), all('table'[HourBlock] ) )

Best Regards,

Dale

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

YES!!!!!!  That was exactly what I needed!  THANK YOU!!!! Smiley Happy

Hi,

 

Could you share the solution to obtain the number of each day within a date range??

 

Thanks.

 

 

@amulder

 

Please see file attached as well

 

7324573.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.