I have daily target hours by person for a given month to put towards a project .
I would like to add Dax to convert this daily amount to a monthly amount based on workdays in a month dynamically.
So if the daily amount is 3 hours, there are 21 days in May, the total for the month of May would be 63 for that person.
Can I somehow do this wth DAX?
this is what I have (daily amt) | (I need these monthly totals by person) | ||
21 workdays | 22 workdays | ||
Name | Daily Hours | May Monthly Total | June Monthly Total |
John | 3 | 63 | 66 |
Sally | 5 | 105 | 110 |
Pam | 6 | 126 | 132 |
Dan | 5 | 105 | 110 |
Thank you!
Solved! Go to Solution.
Hi,
Please check the below measure and the attached pbix file.
I suggest having a calendar table like the attached file.
I assumed the working day only excludes Sat. and Sun.
Monthly total: =
SUM ( Data[Daily Hours] )
* COUNTROWS (
CALCULATETABLE ( 'Calendar', NOT 'Calendar'[Day of Week] IN { 6, 7 } )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below measure and the attached pbix file.
I suggest having a calendar table like the attached file.
I assumed the working day only excludes Sat. and Sun.
Monthly total: =
SUM ( Data[Daily Hours] )
* COUNTROWS (
CALCULATETABLE ( 'Calendar', NOT 'Calendar'[Day of Week] IN { 6, 7 } )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Share the input data table.
User | Count |
---|---|
139 | |
84 | |
62 | |
60 | |
55 |
User | Count |
---|---|
211 | |
108 | |
88 | |
75 | |
72 |