Advanced Dax Help

[Anonymous]

I have a report I am working on that has a table where goals are listed on a monthly basis. I would like to get these goals to appear on a daily, per workday basis. I already have a dates table that has "IsWorkday" as a calculated column which outputs "1" for workdays and "0" for weekends and holidays.

For your information I am also using DirectQuery which limits the possibilities with DAX. I have also disabled the DAX restrictions in the settings, but limitations still exist.

Now onto where I'm currently at:

GoalsperDay = 
CALCULATE(
	SUMX(
		VALUES('dim_dates'[date1]),
		CALCULATE(
			CALCULATE(
				Sum ([Goal]),
				ALL('dim_dates'[date1]),
				SUMMARIZE('dim_dates',dim_dates[month])
			)
				/CALCULATE(
					DISTINCTCOUNT('dim_dates'[date1]),
					ALL('dim_dates'[date1]),
					'dim_dates'[IsWorkday] =1,
					SUMMARIZE('dim_dates',dim_dates[month])
				)
			)
	),'dim_dates'[IsWorkday]=1
)

This formula works almost perfectly... almost.

The problem I continue to face is that I am using relative date filtering in my report as shown here:

This is causing the "GoalsperDay" measure to roll up the entire months goal into however many days we are currently into the month. As an example:

September Monthly Goal = $6,000,000

Working Days in September = 20

I should see: 09/01/2017 Goal = $300,000

But I'm seeing: 09/01/2017 Goal = $6,000,000

If I roll my filter back to August I will see everything laid out correctly with goals allocated per day correctly which makes sense because the month has completed so the measure is seeing every day.

How can I modify that measure so that it divides monthly goals per working days regardless of the relative filter?

Thanks for your time 

[AlbertoFerrari · Accepted solution]

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

[Anonymous]

@AlbertoFerrari,

Thank you so much! I really appreciate that you took the time to look this over.

To be honest on some of the coding issues, I wrote this sort of as an amalgamation of multiple other formulas I found online.

I still have a lot to learn in regards to DAX logic so I used a bunch of pieces from other solutions that I thought would help.

I'm not sure what happened over the weekend, but as of today that measure is working as intended now. I have implemented your suggestions in cleaning up the coding to make it easier to look over.

Thanks again!

[malagari]

@Anonymous I've found that refactoring a measure that I've cobbled together from online resources is the best way to learn what is actually happening in the measure definition.  If you're going to invest time learning Power BI, pull something like this into DAX Studio and deconstruct it to understand what each function returns.  This will help immensely in the future.

And, as @AlbertoFerrari mentioned, thank you for a cohesive question.