DAX Measure to total a count for each weekday within a date range.

[Anthony67]

Hi,

Looking for a DAX measure that calculates the number of lunches to order for a specific day of the week based on the StartDate and EndDate of a course.  Matrix that looks like this:

Course	Sun	Mon	Wed	Thu	Fri	Sat	Total
ABC			1	1	1		3
DEF		1	1				2
Total	0	1	2	1	1	0	5

Start and end dates are in a fact table.

Separate calendar dimension to be used for weekdays.

Hope this request makes sense😊

Basically tried applying this type of logic:

Count the number of delegates where the weekday(calendar[Date] is in the date range Weekday(table[StartDate]) and Weekday(table[EndDate])

Any assistance will be appreciated.

Thanks

[AntrikshSharma]

Try something like:

Total Delegates =
CALCULATE (
    SUM ( Table[Lunches] ),
    WEEKDAY ( Calendar[Dates] ) IN { 1, 2, 3, 4, 5 }
)

[Anthony67]

Thanks - but this measure places the count in all the days and not just those within the date range.

[Anthony67]

 Hi - sorry for error - Tuesday should be included:)

[lbendlin]

Create a measure that for each course and day sets a flag if the course is active that day ( start<=day and day<=end). Then count the flags.

What about courses that run on tuesdays?

[Anthony67]

Hi - Any idea on how to do this  - relatively new to Power BI - would appreciate a pointer.