Create a line graph based on cumulative realized and forecast data

[mcarabbering]

Hello,

I want to create a line graph for 2019 based on cumulative values (see right table Outcome). And I can't seem to get this table Outcome the way I want it. The trick here is that:

• The 1st 8 month shows the totaal value per month. So this is already cumulative.
• The next 4 month shows the forecast data which should be substracted
• However, not for all 4 month I have data (see example FactValue table)

I hope some one can help me out.

Thanks

Martijn

[MFelix]

Hi  @mcarabbering ,

I'm assuming that being your values cumulative that the total value for each month is growing and don't have any decreases (on you example the values are flat). So add the following measure to your model:

Measure =
VAR Forecast_Minimum_Date =
    DATE ( 2019; 9; 1 )
VAR ActualsValue =
    CALCULATE (
        MAX ( FactTable[Value] );
        FILTER (
            ALL ( DimDate[Date] );
            DimDate[Date] <= MAX ( DimDate[Date] )
                && DimDate[Date] <= Forecast_Minimum_Date
        )
    )
VAR ForecastValue =
    CALCULATE (
        SUM ( FactTable[Value] );
        FILTER (
            ALL ( DimDate[Date] );
            DimDate[Date] <= MAX ( DimDate[Date] )
                && DimDate[Date] >= Forecast_Minimum_Date
        )
    )
RETURN
    ActualsValue - ForecastValue

This should give expected result.

Regards,

MFelix

[mcarabbering]

Ha @MFelix,

just a small reply on your assumption:

"I'm assuming that being your values cumulative that the total value for each month is growing and don't have any decreases (on you example the values are flat). So add the following measure to your model"

Thats a wrong assumption. During the months, the value can decrease ánd increase. So, if I read your code correctly this will not work, since the part " MAX ( FactTable[Value] )" does not apply. If we have 200 in february and 50 in july and the rest is 100, then the base value to start with should be 100 at date 1-8-2019. And my guess is that this code will start with 200 from february.

Still, I appreciate all the help you give me, because I am stuck at this point with this formula. I hope you can help me out based on this information and the previous information.

Thanks

[mcarabbering]

Hi @MFelix ,

thanks for your quick response. Sorry for my delayed response :).

I have 1 challenge to run your code, since the FactTable[Value] is a measure and not a column in my Facttable. And therefor I can't use the MAX condition. The code I used to calculated the measure is shown below.

So I was wondering if I have to create a column instead of a measure for FactTable[Value] or is it possible to adjust the code you written and work with the existing measure. In both cases, I am open to solutions. 

Nevertheless, thanks for your help so far!

Kr Martijn

---

Value =

VAR Value= calculate(sum(FactTable[Value]);
                    FILTER(FactTable;(FactTable[status] ="ACTIVE"
                                                    ||left(FactTable[status];4)="Prog"
                                                    ||left(FactTable[status];4)="Real")
                                                    && (FactTable[DevelopDate] <= MAX((FactTable[DevelopDate])
            )
)

return Value