Cumulative Totals with status and scenarios

[Anonymous]

Hi everyone. Hope you are doing well and good.

Struggling to get my cumulative totals for actuals+forecast.

What I need (manually calculated in excel) is [Actual+Forecast YTD] measure that works as on the screenshot:

I have measures for [Actuals], [Forecast], [Actual+Forecast] that works just fine.

Using the following TOTALYTD for cumulative total:

=TOTALYTD ([Actual+Forecast]; dCalendar[Date])

returns the following:

So it recalculates YTD values for different Status (ACT, FRC) individually.

What there is more to this:

• Actual and Forecast are separate tables;
• Status (Actual, Forecast etc) - is a Dimension table that has relationships with fActual and fForecast tables;
• This project for now works only in Excel+PowerPivot, if that is of any importance.

I've tried KEEPFILTERS, REMOVEFILTERS but with no meaningful result.

Could anyone advise me on how to approach the issue?

Thank you in advance.

EDIT:

fActual and fForecast tables have the same structure, but fForecast table has additional column with scenario number which is also  required to produce the following table with YTD data:

EDIT2. Updated TOTALYTD formula to avoid confusion,

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

Not very clear about the table structure and relationships in your model. But you can try the following Measures.

Actual+ForecaseYTD =

VAR total_actual =

    SUMX ( ALL ( fAcutal ), [actual_] )

VAR inter_table =

    ADDCOLUMNS ( fForecast, "total", total_actual )

VAR total__ =

    TOTALYTD ( [Actual+Forecast], dCalendar[Date] )

RETURN

    IF (

        HASONEFILTER ( fForecast[date] ),

        Total__ + MAXX ( inter_table, [total] ),

        Total__

)

Or

Actual+ForecaseYTD =

VAR total_actual =

    SUMX ( ALL ( fAcutal ), [actual_] )

VAR inter_table =

    ADDCOLUMNS ( fForecast, "total", total_actual )

VAR cum_actual =

    CALCULATE (

        [Actual+Forecast],

        FILTER ( ALL ( fAcutal ),fAcutal[date]>=DATE(YEAR(fAcutal[date]),1,1)&& fAcutal[date] <= MAX ( fAcutal[date] ) )

    )

VAR cum_ff =

    CALCULATE (

        [Actual+Forecast],

        FILTER ( ALL ( fForecast ), fForecast[date]>=DATE(YEAR(fForecast[date]),1,1)&&fForecast[date] <= MAX ( fForecast[date] ) )

    )

RETURN

    IF (

        HASONEFILTER ( fAcutal[date] ),

        cum_actual,

        IF ( HASONEFILTER ( fForecast[date] ), cum_ff + MAXX ( inter_table, [total] ) )

    )

The result looks like this:

For more details, you can refer the attached pbix file. If you still have questions, please don't hesitate to let me known.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd]

Hey @Anonymous ,

be aware that the second parameter of TOTALYTD needs a column not a table.

Try to replace that:

=TOTALYTD ([Actual+Forecast]; dCalendar[Date])

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hey, @selimovd.
Yes, I am aware of that. Omitted for simplicity.

Noted, that it is unacceptable practice.
Edited post for that matter.