0

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?

Turch
  • 1,546
  • 2
  • 15
  • 31

1 Answers1

0

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?

Josh Fennessy
  • 86
  • 1