TOTALYTD with Actual & forecast periods

[Anonymous]

Hello, 

I try to solve a tricky TOTALYTD-formula. I did plenty of research but did not find a suitbale solution. An easy TotalYTD-formula is no problem for me. This, for example, works fine: 

TOTALYTD(sum(tbl_base[production]);tbl_date[date];tbl_base[Version]="ACT";"31.10.2022")

Here, I do the rolling sum of all Actual (ACT) values up to 31.10.2022. The year gets selected by a slicer. 

Forecast Versions are also available. Their values start in the first forecast month and dont contain Actual values. For example the FC04 starts in May and the first 4 months are empty. In order to create a TOTALYTD for the complete Forecast I need the first 4 months taken from the ACT Version and the months 5-12 from FC04. 

I tried summing two different TOTALYTD-formulas (one for ACT, one for FC04). It did not work and Im pretty sure, this makes no sense. 

A hint would be nice. Many thanks in advance

Eric

[Anonymous]

Hello, 
here is what Im looking for. I created this in Excel. A running total that starts with four act-months and then 3 Forecast months.

A regular running total works for me (just ACT vlaues for 6 months): 

YTD =TOTALYTD(SUM('tbl_base'[Production]), 'tbl_date'[date],tbl_base[Version]="ACT","30.06.2022")

But how to add the four Forecast months?

This ist what my main table looks like.

And this is the model:

Please find below a link to the file described above.

sample file 

Many thanks in Advance 

Eric

[v-mengzhu-msft]

Hi @Anonymous ,

Is there any available sample data or pbix files without private data available for me to test?

Best regards,

Community Support Team Selina zhu

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

[Anonymous]

Hello,

I would have to prepare that. I do that at the end of the working day. 

Regards

Eric