cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## daily amount converted to monthly amount based on number of workdays in any given month

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!

1 ACCEPTED SOLUTION
Super User

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.

3 REPLIES 3
Post Patron

@Jihwan_Kim perfect solution. Thank you for your time

Super User

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.

Super User

Hi,

Share the input data table.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.