cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Cumulative Data sum by day

Hi Every one,

I developed a calculated column as below for cumulative line to sum actual man hours , but it doesnt sum up at all !

```Cumulative Actual =
CALCULATE (
sum ( 'PBI LabourCode_Actual'[ActualsManHours] ),
FILTER (
ALL ( 'PBI LabourCode_Actual'),
'PBI LabourCode_Actual'[WorkDate] <= MAX ( 'PBI LabourCode_Actual'[WorkDate] )
)
)```

As you can see in the above table, cumulative data is showing wrong numbers

1 ACCEPTED SOLUTION
Super User

Hi,

Create a Calendar Table and build a relationship from the WorkDate column of the PBI LabourCode_Actual Table to the Date column of the Calendar Table.  In your visual, drag Date from the Calendar Table.  Write this measure

Cumulative Actual =
CALCULATE (
sum ( 'PBI LabourCode_Actual'[ActualsManHours] ),DATESYTD(Calendar[Date],"31/12"))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User

Hi,

Create a Calendar Table and build a relationship from the WorkDate column of the PBI LabourCode_Actual Table to the Date column of the Calendar Table.  In your visual, drag Date from the Calendar Table.  Write this measure

Cumulative Actual =
CALCULATE (
sum ( 'PBI LabourCode_Actual'[ActualsManHours] ),DATESYTD(Calendar[Date],"31/12"))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Thanks mate!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

@Anonymous ,

Modify the measure as below and check if it can meet your requirement.

`Cumulative Actual =CALCULATE (SUM ( 'PBI LabourCode_Actual'[ActualsManHours] ),ALLEXCEPT ( 'PBI LabourCode_Actual', 'PBI LabourCode_Actual'[Date] ))`

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

No, unfortunately it doesnt work ! it doesnt sum up . look at the below result.

Community Champion

```Cumulative Actual =
CALCULATE (
sum ( 'PBI LabourCode_Actual'[ActualsManHours] ),
FILTER (
ALL ( 'PBI LabourCode_Actual'),
'PBI LabourCode_Actual'[WorkDate] <= EARLIER ( 'PBI LabourCode_Actual'[WorkDate] )
)
)```

Proud to be a PBI Community Champion

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.