How to create the measure based on Date column in Excel

WEEKDAY() demands a scalar value. You are passing it a column reference. When you ask for WEEKDAY( 'DimDate'[Date] ), you're asking the formula engine to return the weekday of a list of dates? How can it return a single weekday for >1 dates?

What is the purpose of this measure? Typically weekday would be an additional field in the date table, not a measure. You can add a calculated column in your date dimension using the same formula you've proposed. This is because calculated columns are evaluated in row context. In a row context, any column is guaranteed to have one value.