Reply
avatar user
Anonymous
Not applicable

Available days per week (planning)

Hi all,

 

I'm new to the all the DAX formula. And i'm struggeling to find the solution. I want to get the hours available for each consultant grouped by weeks. Like this:

janszoon_0-1597677229811.png

 

Each day (except weekend) the consultants are able to make 8 hours. The consultants want to be able to see which weeks they have room voor new projects. When 8 hours are already planned on a specific day, they have no space left. The planning is recorded in the table Workorder. The days available have to be totaled per week. Unfortunately, I cannot assume that a consultant has 40 hours a week deducted by the planned hours that week. This is because, it is possible that consultant is planned twice in one day (more than 8 hours).

 

I made the following formula thanks to the Forum. But unfortunately it doesn't work properly as you can see. Per date the available hours are correct, but when totaled per week it doesn't work. See below the formula:

 

janszoon_1-1597683692065.png

The table factworkorder[DurationTime] contains the amount of hours planned per date. It is possible that on a specific date there are no hours planned, so the available hours have to be 8 (1 day). Below the outcome of the formula above:

 

janszoon_3-1597683909090.png

How can I achieve this? Thanks in advance!

7 REPLIES 7
avatar user
Anonymous
Not applicable

You can also use the composite model and you'll most likely be able to create any table you want and take data from any source you want.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models
avatar user
Anonymous
Not applicable

Yeah. If you use SQL live connection you can approach your SQL admin and politely ask him to create a view for you with the unpivoted data. It's dead simple to do and you'll be having a much more pleasant time with DAX.
amitchandak
Super User
Super User

@Anonymous , You have unpivot the data and you join with date table having week number to get dates. That can have the standard hours per day too.

https://radacad.com/pivot-and-unpivot-with-power-bi

Can you share sample data and sample output in a table format?

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user
Anonymous
Not applicable

@amitchandak  Thanks! Problem is I'm using the live connection (SQL) database mode. Is there an other way to do this? To deduct the planned hours of the 8 hours per day?

Greg_Deckler
Super User
Super User

@Anonymous - First thing I would do is unpivot your week columns, it will make your DAX far, far easier.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
avatar user
Anonymous
Not applicable

@Greg_DecklerThanks! Problem is I'm using the live connection (SQL) database mode. Is there an other way to do this?

@Anonymous - OK, what does your source data look like? I can think of a couple possible things that could help but I need to understand the format of the source data. 

 

For example, this might be a situation for multi-column aggregation. https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-p/391698#M129

 

Or it might be a measure totals problem - 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150

 

Hard to tell



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)