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 measure for material cost as below:

 

Cumulative Material Cost =

VAR _Max = MAX('LATIS Material Data'[G/L Date])

return

    Calculate(sum('LATIS Material Data'[Amount]), ALLSELECTED('LATIS Material Data'), 'LATIS Material Data'[G/L Date] <= _Max)

 

I have a similar different dataset and cumulative measure for labour cost and both perform as expected when plotted on a graph for cumulative total over time. 

 

I am looking to create a total cumulative measure, combining both material and labour into a single line that can be plotted on a graph. The two datasets both have relationships with the same common "DATES" table and I have tried various soluions from similar use cases from this website but none are working exactly. Here is an example of one I ahve tried but most give me a result as per below graph:

Combined Cost =
VAR total1 = 'LATIS Material Data'[Cumulative Material Cost]
VAR total2 = 'Timesheet Data Sept2020-2023'[Cumulative Labour Cost]
VAR _Max = MAX('Timesheet Data Sept2020-2023'[Date])
RETURN
Calculate((total1 + total2), allselected(Dates[Master Date]), Dates[Master Date] <= _Max)

Ignore the blue line, the red line is the cumulative labour line and purple is supposedly the cumulative combined line. For each date where there is a material cost, it either gives a spike up to the combined value on that date or, if there is a date with a material cost but no labour cost, a dip down to just the material cost on that date. For every other date, the purple just matches the red (labour line).

 

Please let me know if there is an easy measure I can use for this, I am doing something silly, or this isn't possible withour combining the two datasets (they are huge and are managed very independently).

Thanks in advance!

  • 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
    )

2 Replies

  • 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

      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.