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
jeffgarlisch
Helper I
Helper I

Count number of Fridays,Saturday,Sundays between specified date ranges

Hey All, 

 

 

I have sales data that is on a daily level, structured like below.

I need to dynamically count the number of Fri,Sat,and Sun respectively between a specifed date range in the current year and in same range for the previous year, this table is linked to a date dimension table.  

 

e.g between Jan 1st 2018 and February 1st 2018 there were 4 Fri,4 sat,4 Sun

e.g between Jan 1st 2019 and February 1st 2019 there will be 4 Fri,4 sat,4 Sun

I need to show this count, to help explain variances in YOY sale volume on a daily basis, say in the event that one month had 4 saturdays, and the previous year same month had 3 saturdays, thats one less busy shopping day included in my calculations.

 

 

 

Capture.JPG

 

 

Any help would be appreciated!

2 REPLIES 2
ChrisMendoza
Resident Rockstar
Resident Rockstar

@jeffgarlisch -

 

Not 100% sure on how you want the result displayed/used but, here are two options that you may find useful (using a proper Date Calendar):

 

Dynamic w/ Slicer = 
    CALCULATE (
        COUNT ( tblCalendar[CalendarDate] ),
        FILTER (
            tblCalendar,
            WEEKDAY ( tblCalendar[CalendarDate], 2 ) >= 5
        )
    )
Static = 
VAR sDate1 =
    DATE ( 2018, 1, 1 )
VAR eDate1 =
    DATE ( 2018, 2, 1 )
VAR sDate2 =
    DATE ( 2019, 1, 1 )
VAR eDate2 =
    DATE ( 2019, 2, 1 )

VAR fss2018 =
    CALCULATE (
        COUNT ( tblCalendar[CalendarDate] ),
        FILTER (
            DATESBETWEEN ( tblCalendar[CalendarDate], sDate1, eDate1 ),
            WEEKDAY ( tblCalendar[CalendarDate], 2 ) >= 5
        )
    )
VAR fss2019 =
    CALCULATE (
        COUNT ( tblCalendar[CalendarDate] ),
        FILTER (
            DATESBETWEEN ( tblCalendar[CalendarDate], sDate2, eDate2 ),
            WEEKDAY ( tblCalendar[CalendarDate], 2 ) >= 5
        )
    )
RETURN fss2018 - fss2019

Yields the following:

 

2018.PNGchange title.PNG

Another Dynamic Option:

 

Dynamic w/ Slicer 2 =
VAR fss =
    CALCULATE (
        COUNT ( tblCalendar[CalendarDate] ),
        FILTER (
            DATESBETWEEN (
                tblCalendar[CalendarDate],
                FIRSTDATE ( tblCalendar[CalendarDate] ),
                LASTDATE ( tblCalendar[CalendarDate] )
            ),
            WEEKDAY ( tblCalendar[CalendarDate], 2 ) >= 5
        )
    )
VAR fssSPLY =
    CALCULATE (
        COUNT ( tblCalendar[CalendarDate] ),
        FILTER (
            DATESBETWEEN (
                tblCalendar[CalendarDate],
                SAMEPERIODLASTYEAR ( FIRSTDATE ( tblCalendar[CalendarDate] ) ),
                SAMEPERIODLASTYEAR ( LASTDATE ( tblCalendar[CalendarDate] ) )
            ),
            WEEKDAY ( tblCalendar[CalendarDate], 2 ) >= 5
        )
    )
RETURN
    fss - fssSPLY





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



AlB
Super User
Super User

Hi @jeffgarlisch

 

If you have a Date table, try these measures while selecting the date period of interest in a slicer with 'Date'[Date]

 

NumFridays = CALCULATE(COUNT(Sales[DayName]),Sales[DayName]="Friday")

NumFridaysLY = CALCULATE([NumFridays], PREVIOUSYEAR('Date'[Date]))

 

You can follow the same approach for the other days.

 

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.