Sum of a value per task per days

[gabitu]

Hi, I am trying to create a measure summarizing the effort hours (a decimal type) per date (a day) for a given task (a string type)

I have the table tblTasks containing the taskID, taskName, taskEffortHours, and the second table called tblCalendar containing calendarID, taskID, calendarDate. The tables are joined on taskID collumn. 

If I create a card in Power BI with Sum of taskEffortHours and using a date slider where I select the days, but the card is not displaying the sum correctly, it just sumarrizes the taskName names but not the days. That's why I need this measure that will calculate the effortHours per tasks and per days.

Please help with this, I spent days trying to figure out this measure.

Many thanks!

[Sahir_Maharaj · Accepted solution]

Hello @gabitu,

You can use the SUMX function in DAX along with FILTER to iterate through the tblCalendar table and calculate the sum of effort hours for each day and task combination.

Effort Hours Per Task Per Day = 
SUMX(
    VALUES(tblCalendar[calendarDate]), 
    CALCULATE(
        SUM(tblTasks[taskEffortHours]),
        FILTER(
            tblCalendar,
            tblCalendar[calendarDate] = EARLIER(tblCalendar[calendarDate])
        )
    )
)

Should you require further assistance please do not hesitate to reach out to me.

[gabitu]

Thank you Sahir, this works as charm!

[Sahir_Maharaj · Accepted solution]

Hello @gabitu,

You can use the SUMX function in DAX along with FILTER to iterate through the tblCalendar table and calculate the sum of effort hours for each day and task combination.

Effort Hours Per Task Per Day = 
SUMX(
    VALUES(tblCalendar[calendarDate]), 
    CALCULATE(
        SUM(tblTasks[taskEffortHours]),
        FILTER(
            tblCalendar,
            tblCalendar[calendarDate] = EARLIER(tblCalendar[calendarDate])
        )
    )
)

Should you require further assistance please do not hesitate to reach out to me.

[gabitu · Accepted solution]

Thank you Sahir, works as charm!

[gabitu]

Thanks you very much Sahir, I will try!