How to achieve SUMPRODUCT result in Excel using DAX

[Kasunpathirana]

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. 🙂 

[Kasunpathirana · Accepted 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. 

[v-yulgu-msft]

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

[Kasunpathirana]

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. 

[SivaMani]

Please refer the below DAX function,

SUMX() - https://msdn.microsoft.com/en-us/query-bi/dax/sumx-function-dax

[Kasunpathirana]

@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.