How can i sum this data without the overlapping values

[nicoenz]

hi everyone,

I want to create a simple sum function that allows me to select the Version (with a slicer) and sums the forecasted and the actual "Contr_Margin values" excluding overlaps.

I have the following table where Versions (Actual, Forecast May, Forecast June, etc). Actual dates have been recoded with 01/01/1900 so they can be used in any month:

Versions	Versions_Date	Contr_Margin	Prod_Code	Fact_Date
Actual	01/01/1900	531	123	03/01/2023
Actual	01/01/1900	109	123	04/01/2023
Actual	01/01/1900	61	123	05/01/2023
Actual		225	123	06/01/2023
Actual		260	123	07/01/2023
Actual		260	123	08/01/2023
Actual		158	123	09/01/2023
Actual		204	123	10/01/2023
Actual		647	123	11/01/2023
Actual		102	123	12/01/2023
Actual		240	123	01/01/2024
Actual		306	123	02/01/2024
Actual		340	123	03/01/2024
Actual		266	123	04/01/2024
Actual	01/01/1900	245	123	05/01/2024
Actual	01/01/1900	306	123	06/01/2024
Actual	01/01/1900	340	123	07/01/2024
Fcst   May	05/01/2024	99	123	06/01/2024
Fcst   May	05/01/2024	204	123	07/01/2024
Fcst   May	05/01/2024	306	123	08/01/2024
Fcst   May	05/01/2024	306	123	09/01/2024
Fcst   May	…	531	123	10/01/2024
Fcst   May		61	123	11/01/2024
Fcst   May		225	123	12/01/2024
Fcst   May		204	123	01/01/2025
Fcst   May		145	123	02/01/2025
Fcst   May		138	123	03/01/2025
Fcst   May		526	123	04/01/2025
Fcst   May		222	123	05/01/2025
Fcst   May		666	123	06/01/2025
Fcst   May		466	123	07/01/2025
Fcst   May		357	123	08/01/2025
Fcst   May		268	123	09/01/2025
Fcst   May	05/01/2024	486	123	10/01/2025
Fcst   May	05/01/2024	548	123	11/01/2025
Fcst   May	05/01/2024	266	123	12/01/2025
Fcst   June	06/01/2024
Fcst   June	06/01/2024
Fcst   June

I tried many different ways of summing my data but always get the same result. Whatever i try its sums overlapping values (June,July in Forecast May version).

Below pivot table shows what I get and what I would like to get:

Sum of Contr_Margin	Versions
Fact_Date	Actual	forecast May2024	my results	Expected result
03/01/2023	531		531	531
04/01/2023	109		109	109
05/01/2023	61		61	61
06/01/2023	225		225	225
07/01/2023	260		260	260
08/01/2023	260		260	260
09/01/2023	158		158	158
10/01/2023	204		204	204
11/01/2023	647		647	647
12/01/2023	102		102	102
01/01/2024	240		240	240
02/01/2024	306		306	306
03/01/2024	340		340	340
04/01/2024	266		266	266
05/01/2024	245		245	245
06/01/2024	306	99	405	306
07/01/2024	340	204	544	340
08/01/2024		306	306	306
09/01/2024		306	306	306
10/01/2024		531	531	531
11/01/2024		61	61	61
12/01/2024		225	225	225
01/01/2025		204	204	204
		Sum:	6'536	6'233

Any help will be greatly appreciated!

[ryan_mayu · Accepted solution]

@nicoenz 

pls see if this is what you want

pls see the attachment below

[nicoenz]

thanks!!!!! @ryan_mayu 

[ryan_mayu]

you are welcome

[nicoenz]

it works perfectly well the way you have it.

i tried integrating everything into a single measure and i get the wrong column total. any idea why?
i defined:

Measure 2 =     

var actual2 = CALCULATE(sum('Table (2)'[Contr_Margin]),'Table (2)'[Versions]="Actual")

var fc = CALCULATE(sum('Table (2)'[Contr_Margin]),'Table (2)'[Versions]="Fcst May")

var measure = if(ISBLANK([actual2]),[fc],[actual2])

return

sumx(values('Table (2)'[Fact_Date]),[Measure])

Can all be integrated into a single measure?

[ryan_mayu · Accepted solution]

have to separate the measures. That will not work if you combine two measures.

[ryan_mayu · Accepted solution]

@nicoenz 

pls see if this is what you want

pls see the attachment below