Getting workdays for the year in Power Pivot DAX

0
votes

I need to use the full year's work days in my DAX calculation, but I can't figure out how to get DAX to do that. I have two tables,

1) sales with their respective dates

2) work days table, with end of month and the month's respective work days

I created the relationship between the two, based on the date, and it works, however DAX captures workdays only for the month's where there were sales. How do I get the full year's sum?

As an example, these are the two tables I created: enter image description here

and connected them as follows: enter image description here

I then added a calculated field with the following DAX:

=CALCULATE( SUM(tWorkdays[Days]), FILTER( tSales, YEAR(tSales[Date]) = YEAR(MAX(tSales[Date])) ) )

and built a pivot table like so: enter image description here

As you can see, it just captures workdays for the months where there were sales, but I need the entire total, regardless if there were any sales or not.

What can I do to fix this?

1
exceldaxpowerquery
can you please show a desired output. you might have to look into All() or EXCEPT() – Roger Steinberg

1 Answers

0
votes

I'm not positive about the details, but it has something to do with ALL(), EXCEPT(), and VALUES()

A matter of context, as they say in DAX

https://powerpivotpro.com/2011/06/precedence-part-3-allexcept-vs-all-w-values/

And another explanation: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/