Conditional Cumulative Sum of the Actual and Forecast Data

[Anonymous]

Hello Guys,

I am looking for cumulative sum of the where I need to add Forecast values when Actuals ate not there and take cumulative of that measure.

Here is the sample data:

Year	Month	Generation Actual MTD	Generation Forecast MTD_Graph	Generation Actual MTD_Grpah	Cumulative_Actual YTD
2022	January	4,47,986	9,89,638	4,47,986	4,47,986
2022	February	0	9,96,152	9,96,152	14,44,138
2022	March	0	11,66,350	11,66,350	26,10,488
2022	April	0	12,24,855	12,24,855	38,35,343
2022	May	0	13,90,229	13,90,229	52,25,571
2022	June	0	14,28,481	14,28,481	66,54,053
2022	July	0	14,82,140	14,82,140	81,36,193
2022	August	0	14,25,575	14,25,575	95,61,768
2022	September	0	12,63,024	12,63,024	1,08,24,791
2022	October	0	12,13,457	12,13,457	1,20,38,248
2022	November	0	10,53,838	10,53,838	1,30,92,087
2022	December	0	9,85,419	9,85,419	1,40,77,506

Measures used in above table:

Generation Actual MTD_Grpah =

IF([Generation Actual MTD]<>0, [Generation Actual MTD],

[Generation Forecast MTD_Graph])

Expected result is in the "Cumulative_Actual YTD" column - this is where I need help to create measure. Please note that I need measure only not a calculated column. I need to take cumulative of the column where we have taken Forrecast whenever Actuals are not available.

Thanks,

Ram

[Anonymous]

Hello Guys,

I am looking for cumulative sum of the where I need to add Forecast values when Actuals ate not there and take cumulative of that measure.

Here is the sample data:

Year	Month	Generation Actual MTD	Generation Forecast MTD_Graph	Generation Actual MTD_Grpah	Cumulative_Actual YTD
2022	January	4,47,986	9,89,638	4,47,986	4,47,986
2022	February	0	9,96,152	9,96,152	14,44,138
2022	March	0	11,66,350	11,66,350	26,10,488
2022	April	0	12,24,855	12,24,855	38,35,343
2022	May	0	13,90,229	13,90,229	52,25,571
2022	June	0	14,28,481	14,28,481	66,54,053
2022	July	0	14,82,140	14,82,140	81,36,193
2022	August	0	14,25,575	14,25,575	95,61,768
2022	September	0	12,63,024	12,63,024	1,08,24,791
2022	October	0	12,13,457	12,13,457	1,20,38,248
2022	November	0	10,53,838	10,53,838	1,30,92,087
2022	December	0	9,85,419	9,85,419	1,40,77,506

Measures used in above table:

Generation Actual MTD_Grpah =

IF([Generation Actual MTD]<>0, [Generation Actual MTD],

[Generation Forecast MTD_Graph])

Expected result is in the "Cumulative_Actual YTD" column - this is where I need help to create measure. Please note that I need measure only not a calculated column. I need to take cumulative of the column where we have taken Forrecast whenever Actuals are not available.

Thanks,

Ram

[v-xiaoyan-msft]

Hi @Anonymous ,

I'm not quite sure if I understand your needs accurately. Would you mind telling me a more detailed description of your needs so that I can help you better?

Hope it helps,

Community Support Team _ Caitlyn

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

[Anonymous]

Guys, please help!!

[Anonymous]

Can you please help me with a working .pbix file.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please try the following measure:

Cumulative_Actual YTD = 
SUMX (
    FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) ),
    [Generation Actual MTD_Grpah]
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , assume you have measures like

M1= Sum(Table1[Actual])

M2= Sum(Table2[Forecast])

then create measure like
calculate( sumx(values('Date'[Month Year]), if( isblank([M1]), [M2], [M1])), datesytd('Date'[date]))