Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Below I have shown my sample data and the expected result. In excel it's pretty much straight forward.
My challenge is how we use DAX to accomplish this I have created a DATE table and the rest of the Item records imported.
I was looking for help on how to achieve this using DAX. 🙂
Solved! Go to Solution.
There are few ways to achieve this.
I should Thank @marcorusso, @MarcelBeug and @Phil_Seamark for the wonderful content.
Below is my source:
Below Result Can be Achieved from the M and DAX Code
You can use either M or DAX I prefer the M way which is faster but it can also be solved using DAX.
For the M Code you can refer the post https://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/td-p/129418 and find @MarcelBeug video
Below is the DAX Code I used this helps to expand the table by the Start Date and End Date.
Resource Requirement Expanded =
SUMMARIZE(
GENERATE(
'Resource Requirement',
CALCULATETABLE(
VALUES('Date'[Date]),
DATESBETWEEN('Date'[Date],'Resource Requirement'[Start Date],'Resource Requirement'[End Date])
)
),
'Date'[Date],
'Resource Requirement'[Project],
'Resource Requirement'[Required Number of Resources Per Day]
)
If you are only looking at a DAX Measure you can use below. (Note Only can use to sum up the daily Resource Requirement if want to achieve the Matrix need to use above DAX Code. Thanks to @marcorusso for this guidng me for this.
Also recommend to read SQLBI Article https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
Utilization Measure =
VAR SelectedDay = SELECTEDVALUE ( 'Date'[Date] )
RETURN
CALCULATE
(
SUM ( 'Resource Requirement'[Required Number of Resources Per Day]),
'Resource Requirement'[Start Date]<= SelectedDay,
'Resource Requirement'[End Date] >= SelectedDay,
ALL ( 'Resource Requirement'[Required Number of Resources Per Day]) )
Love the Power BI Community.
There are few ways to achieve this.
I should Thank @marcorusso, @MarcelBeug and @Phil_Seamark for the wonderful content.
Below is my source:
Below Result Can be Achieved from the M and DAX Code
You can use either M or DAX I prefer the M way which is faster but it can also be solved using DAX.
For the M Code you can refer the post https://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/td-p/129418 and find @MarcelBeug video
Below is the DAX Code I used this helps to expand the table by the Start Date and End Date.
Resource Requirement Expanded =
SUMMARIZE(
GENERATE(
'Resource Requirement',
CALCULATETABLE(
VALUES('Date'[Date]),
DATESBETWEEN('Date'[Date],'Resource Requirement'[Start Date],'Resource Requirement'[End Date])
)
),
'Date'[Date],
'Resource Requirement'[Project],
'Resource Requirement'[Required Number of Resources Per Day]
)
If you are only looking at a DAX Measure you can use below. (Note Only can use to sum up the daily Resource Requirement if want to achieve the Matrix need to use above DAX Code. Thanks to @marcorusso for this guidng me for this.
Also recommend to read SQLBI Article https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
Utilization Measure =
VAR SelectedDay = SELECTEDVALUE ( 'Date'[Date] )
RETURN
CALCULATE
(
SUM ( 'Resource Requirement'[Required Number of Resources Per Day]),
'Resource Requirement'[Start Date]<= SelectedDay,
'Resource Requirement'[End Date] >= SelectedDay,
ALL ( 'Resource Requirement'[Required Number of Resources Per Day]) )
Love the Power BI Community.
Hi @Kasunpathirana,
From above screenshot, which is source data? Which is desired result that returned by SUMPRODUCT? Also, could you please illustrate the expression you used above to help us better understand the calculation rule?
Regards,
Yuliana Gu
Thanks for the Reply @v-yulgu-msft the source data is the one label as Data.
Then I'm expecting the result which is shown on top as result. In excel if use SUMPRODUCT can achieve this result.
Please refer the below DAX function,
SUMX() - https://msdn.microsoft.com/en-us/query-bi/dax/sumx-function-dax
@SivaMani I'm familiar with this funcion. But so far using SUMX() I'm not able to achieve this. Is it possible for you to send the full code to get the result shown.
You can use my sample data to show case.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |