Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Any help would be appreciated!
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:
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
Proud to be a Super User!
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |