Forecast - Cumulative Totals taking the most recent month as benchmarking for coming period

[ngrulovic]

Dear Friends,

I would very much appreciate it if you can help me with the following:

Namely I would like to make measure cumulative totals where for past months it will be used actuals and for the future months it would take the actual from the most recent month.

In the example below I have Jan 2022 as the most recent month, and I would like that this amount is used for future months for calculating cumulative. I have shared also the link of the file

https://drive.google.com/file/d/1EiT1PD8v3fLD5WMsALOXqsVheNIKGSDb/view?usp=sharing

[ngrulovic · Accepted solution]

Hello 🙂

Thank you very much for offered solution. It is works partially.  There are still two problems that I see. First, you see that in 2021 you have cumulative values which are not correspondings to desired values as it is in column Value. Also when you add slicer and select some period witinin 2022 I get the wrong cumulative values. Please see the screen below

[v-kkf-msft]

Hi @ngrulovic ,

Please try the following formula:

Cumulative Sales = 
VAR maxDate = MAXX ( ALLSELECTED ( Sales ), Sales[Dates] )
VAR factvalue =
    CALCULATE (
        SUM ( Sales[Value] ),
        FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
    )
VAR Predictedvalue =
    factvalue
        + DATEDIFF (
            MAXX ( ALLSELECTED ( Sales ), Sales[Dates] ),
            MAX ( Dates[Date] ),
            MONTH
        )
            * CALCULATE (
                SUM ( Sales[Value] ),
                FILTER ( ALLSELECTED ( Sales ), Sales[Dates] = maxDate )
            )
RETURN
    Predictedvalue

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.

[ngrulovic · Accepted solution]

Hello 🙂

Thank you very much for offered solution. It is works partially.  There are still two problems that I see. First, you see that in 2021 you have cumulative values which are not correspondings to desired values as it is in column Value. Also when you add slicer and select some period witinin 2022 I get the wrong cumulative values. Please see the screen below

[v-kkf-msft · Accepted solution]

Hi @ngrulovic ,

I modify the measure, please try it.

Cumulative Sales = 
VAR maxDate =
    MAXX ( ALL ( Sales ), Sales[Dates] )
VAR factvalue =
    CALCULATE (
        SUM ( Sales[Value] ),
        FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
    )
VAR Predictedvalue =
    factvalue
        + DATEDIFF ( MAXX ( ALL ( Sales ), Sales[Dates] ), MAX ( Dates[Date] ), MONTH )
            * CALCULATE (
                SUM ( Sales[Value] ),
                FILTER ( ALL ( Sales ), Sales[Dates] = maxDate )
            )
RETURN
    IF (
        FORMAT ( MIN ( Dates[Period] ), "yyyymm" ) > FORMAT ( maxDate, "yyyymm" ),
        Predictedvalue,
        factvalue
    )

           

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.

[parry2k]

@ngrulovic check this video on my youtube channel https://youtu.be/9Mj592bSKq0

Tweak the solution as you see fit.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.