Networkdays issue when totalling for Year level

[mp390988]

Hi,

I have this calculated column in my dim date that displays the networkday for that particular month.

I am running into issues when I create the following matrix visual, as you can see the figures are not displaying correctly at the year level. Ideally, what I want at the year level is the sum of the individual networkdays thus far. So since the visual only has months from Jan to Mar, it should sum up 23+20+21 = 64.

Does anyone have the solution please?

[Anonymous]

Hi @mp390988,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @mp390988,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @mp390988,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[saritasw · Accepted solution]

Hi @mp390988 ,

Thanks for sharing the screenshots.

Your DAX calculated column WorkingDaysPerMonth is correctly computing working days per month, but since it's a calculated column in the date table, using MAX(WorkingDaysPerMonth) in your matrix causes the year-level total to just show the max of the months (23), not the sum.

You want the year-level total to be the sum of working days for the visible months (Jan to Mar), which should be: 

23 (Jan) + 20 (Feb) + 21 (Mar) = 64

In my opinion, You should create a measure, not rely on the calculated column in the matrix.
DAX Measure:
TotalWorkingDays =
SUMX(
VALUES('dim Date'[MonthNum]),
MAX('dim Date'[WorkingDaysPerMonth])
)
Try it.