Forum Discussion
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:
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
- bhanu_gautamSuper User
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
)- Adrian88New 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.