Count Number of Fridays MTD

[MojoGene]

I am trying to count the number of Fridays to date in the current month. Not sure where I am going wrong with this:

Count Fridays MTD = CALCULATE(COUNTROWS(DATESBETWEEN(Table_ExtendedCalendar[DateKey],STARTOFMONTH(Table_ExtendedCalendar[DateKey]),TODAY())),FILTER(Table_ExtendedCalendar,Table_ExtendedCalendar[DayOfWeekMon]=5))

The column "DayOfWeekMon" gives a numerical value to each day of the week from 1 to 7 with Monday being number 1.

What is being returned is the total count of days in the month to the current date.

[AlbertoFerrari · Accepted solution]

Hi,

First of all. Bookmark www.daxformatter.com, and pleeeease, next time format your code before sending it. My geeky soul suffers when it sees DAX written in a single line. 🙂

That said, time intelligence functions, if the relationship is based on a date column, will perform an automatic ALL on the date table to simplify the authoring of time intelligence calculations. However while doing it, they break the normal behavior of filters. Thus, whenever you use time intelligence calculations, you need to remember that they will break filters.

The best way to handle this is to apply filters AFTER you used them, like in the following code, that computes the MTD Fridays:

NumOfFridays Correct =
CALCULATE (
    CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[Day of Week Number] = 5 ),
    DATESMTD ( 'Date'[Date] )
)

As you can see, there are TWO CALCULATE. The outer one applies MTD and, in doing it, it kills any filter. The inner CALCULATE further applies a filter for the Fridays.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[MojoGene]

Alberto:

Ah! I didn't realize that about the time intelligence functions. Thanks.

PS: I am honored to receive advice from the realm of the DAX gods and I'll be sure to use daxformatter.com.

[AlbertoFerrari]

Ahah!, I really wish that was a realm of gods, if you ask my kid about his father's work, he will answer: "dad? I don't really know, he spends the whole day typing stuff on the PC... I think he's a nerd..." 🙂

Anyway, the feature is needed because, otherwise, something like:

CALCULATE ( ..., DATESYTD ( Date[Date] ) )

would always require to be written this way:

CALCULATE ( ..., DATESYTD ( Date[Date] ), ALL ( Date ) )

By performing the automatic ALL, most time intelligence calculations work easily but. then, calculations like yours require very special care.

Have fun with DAX!1

Alberto Ferrari
http://www.sqlbi.com