cancel
Showing results for
Did you mean:
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

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors