Using Networkdays in further calculations

[kbart]

I'm working on rebuilding an Excel dashboard in Power BI and have gotten everything done except for measuring points.  My main data set is an export from an application that records various dates when events occur.  It does not have start and end dates in it.  I have created a date dimension table to sum up how many of these events occur each month.  The last part I have to do is caculate how many working days are in a month then mutiply that by 2 and compare that number to the total points receieved for all other events.

  I created a seperate measure table that is calculating all my points currently.  However when I try to use the networkday function to call my date dimension table it will not work.  I have tried both new column and new measure.  

  I ended up creating another date dimension table in SQL and brought that in.  This table has a field for holiday, weekend and if it's a working day (not holiday or weekend).  I can use the networkday function on that table as a column only and it works, measure will not.  When I try to use that number and do a calculation, muptiply by 2, it will fail.  I am newer to PowerBI and what I gather is you can only use measures with measures.   

 My end goal is to figure out how many working days are in each month and multiple by 2.  I then compare that number against their total points, in my measure table.  Depending on the result another calucation is made to determine payout.  I've been reading blogs and watching videos for 3 days straight and have not found anything that works with my requirements.  Any suggestions are greatly appreciated.

[kbart · Accepted solution]

@lbendlin , I do have the option to edit the date dimension table.  I was able to get this to finally work by joining the date fields from both date dimension tables.  I believe that is part of what is messing up my data having two tables like this.  I did not have a SQL end database at first when I was creating this report.  I'm going to have to rebuid this fully using the SQL date dim table next which should prevent any date issues I was seeing.

  Since my oringial data only has an event date, my ending formula ended up looking like this using datesbetween:  WorkingDayPoints = CALCULATE(COUNTA(DimDatenew[WorkDay]),DATESBETWEEN('Data'[Initial Decision Date],STARTOFMONTH('Data'[Initial Decision Date]),ENDOFMONTH('Data'[Initial Decision Date])))

[lbendlin]

I ended up creating another date dimension table in SQL and brought that in.  This table has a field for holiday, weekend and if it's a working day (not holiday or weekend). 

you can do that - or you could augment your existing calendar table by indicating which days are working days. Would that be an option?

[Anonymous]

Hi @kbart ,

Can you give me sample data or PBIX file without private data if it is convenient. This will make it easier for me to do tests and calculations. Thank you in advance~

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.