cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
electrobrit
Post Patron
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 workdays22 workdays
NameDaily HoursMay Monthly TotalJune Monthly Total
John36366
Sally5105110
Pam6126132
Dan5105110



Thank you!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
electrobrit
Post Patron
Post Patron

@Jihwan_Kim perfect solution. Thank you for your time

Jihwan_Kim
Super User
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.


Go to My LinkedIn Page


Ashish_Mathur
Super User
Super User

Hi,

Share the input data table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

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

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

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