CODEWITHSUNDEEP
ssastabulardax
Turch
Turch

Reputation: 1546

How to filter DATESBETWEEN based on column value (to calculate number of business days in a month)

Working on an SSAS Tabular project in Visual Studio 2010;

I'm trying to create a measure that calculates the total number of business days in a month:

I have Month Start Date and Month End Date measures, and Date and Is Business Day columns.

I can create a Total Business Days measure with COUNTROWS(FILTER(Dates,Dates[Is Business Day]=TRUE())). That gives me the number of business days in the context, but I want the number of business days for the current month.

I've tried various combinations of FILTER, COUNT, COUNTX, COUNTROWS, DATESBETWEEN, and CALCULATE without success.

What I want is a count of days between two dates, where the column [Is Business Day] is true, but I can't seem to get the right combination of filtering.

I would guess I filter the Dates table the way I do for the Total Business Days measure, but FILTER returns a table and COUNTROWS expects a single column - is there a way to get single column from a FILTER result?

Upvotes: 0

Views: 1563

Answers (1)

Josh Fennessy
Josh Fennessy

Reputation: 86

Here's one thought...

First, create a calculated column called MonthKey (if you don't have it already):

=YEAR([Date]) * 100 + MONTH([Date])

Then create another calculated column called IsCurrentMonth

=IF(YEAR(TODAY()) * 100 + MONTH(TODAY()) = [MonthKey], 1, 0)

Then you can create your calculated measure as

COUNTROWS(FILTER(Dates,Dates[IsCurrentMonth] = 1))

Would that do what you need?

Upvotes: 0

Related Questions