Advanced Dax Help

This is pretty simple. Add a calculated column to your date table that basically tells if the date is in the future or not. Something like

IsPast = Date[Date] < TODAY ()

or

IsPast = Date[Date] < MAX ( FactTable[Date] )

With the column in place, you can use it in the denominator to further restrict your working days count to only the days that are actually in teh past, avoiding the extra days in the future.

Some notes on your coding style, could not help doing it while reading :)

• DiSTINCTCOUNT ( Date[Date] ) can be expressed in an easier way with a COUNTROWS ( Date )
• SUMMARIZE ( Date, Date[Month] ) can be replaced with VALUES ( Date[Month] )
• Instead of using the / operator, you can use DIVIDE, which protects from division by zero and lets you indent the code in a better way
• Please format the code with www.daxformatter.com, I know it is very geeky, but it makes my life easier if I read code correctly formatted
• I did not spend too much time on the code, but what is the purpose of the SUMX iterating over dates and then computing values day by day with SUM? Either you iterate with SUMX over a FILTER, or you use CALCULATE, a quick look at the code says that it is a too-complex formula, you can express it in an easier way

Besides, congratulations for a very well-written question, I love when I can read the text once, understand the problem and try to provide an answer! :)

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com