cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CRamirez Regular Visitor
Regular Visitor

Advanced Dax Help

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:

dates-filter.PNGThis 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 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: 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

Alberto Ferrari - SQLBI

View solution in original post

3 REPLIES 3
Highlighted

Re: 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

Alberto Ferrari - SQLBI

View solution in original post

CRamirez Regular Visitor
Regular Visitor

Re: Advanced Dax Help

@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 Member
Member

Re: Advanced Dax Help

@CRamirez 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.

Dan Malagari
Consultant at Headspring

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 151 members 1,445 guests
Please welcome our newest community members: