Forum Discussion

Adrian88's avatar
Adrian88
New Member
1 year ago
Solved

Combining Cumulative measure from different datasets

Hi all,   I have a large report with multiple datasets from different locations that are used for project reporting and performance. I have a dataset for material cost and have created a cumulative...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Adrian88 To create a total cumulative measure that combines both material and labour costs into a single line, you need to ensure that the calculation considers both datasets correctly and accumulates the values over time.

     

    DAX
    Cumulative Material Cost =
    VAR _MaxDate = MAX('LATIS Material Data'[G/L Date])
    RETURN
    CALCULATE(
    SUM('LATIS Material Data'[Amount]),
    ALLSELECTED('LATIS Material Data'),
    'LATIS Material Data'[G/L Date] <= _MaxDate
    )

     

    DAX
    Cumulative Labour Cost =
    VAR _MaxDate = MAX('Timesheet Data Sept2020-2023'[Date])
    RETURN
    CALCULATE(
    SUM('Timesheet Data Sept2020-2023'[Amount]),
    ALLSELECTED('Timesheet Data Sept2020-2023'),
    'Timesheet Data Sept2020-2023'[Date] <= _MaxDate
    )

     

    DAX
    Combined Cumulative Cost =
    VAR _MaxDate = MAX(Dates[Master Date])
    RETURN
    CALCULATE(
    [Cumulative Material Cost] + [Cumulative Labour Cost],
    ALLSELECTED(Dates),
    Dates[Master Date] <= _MaxDate
    )

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Adrian88 To create a total cumulative measure that combines both material and labour costs into a single line, you need to ensure that the calculation considers both datasets correctly and accumulates the values over time.

 

DAX
Cumulative Material Cost =
VAR _MaxDate = MAX('LATIS Material Data'[G/L Date])
RETURN
CALCULATE(
SUM('LATIS Material Data'[Amount]),
ALLSELECTED('LATIS Material Data'),
'LATIS Material Data'[G/L Date] <= _MaxDate
)

 

DAX
Cumulative Labour Cost =
VAR _MaxDate = MAX('Timesheet Data Sept2020-2023'[Date])
RETURN
CALCULATE(
SUM('Timesheet Data Sept2020-2023'[Amount]),
ALLSELECTED('Timesheet Data Sept2020-2023'),
'Timesheet Data Sept2020-2023'[Date] <= _MaxDate
)

 

DAX
Combined Cumulative Cost =
VAR _MaxDate = MAX(Dates[Master Date])
RETURN
CALCULATE(
[Cumulative Material Cost] + [Cumulative Labour Cost],
ALLSELECTED(Dates),
Dates[Master Date] <= _MaxDate
)

Adrian88's avatar
Adrian88
New Member
1 year ago

Thanks bhanu_gautam , that does seem to have worked, I thought I had already tried something very similar but there must have been a subtle difference.