Forecast Total Issue

Hi,

I seem to have an issue with the overall totals in my forecasting table. It doesn’t display the the correct total. I have followed the tutorials around this and reviewed other posts in the forum but can’t seem see what the issue is. It seems to exclude FY2022 figures completely in the overall total.

image

Forecast Calc = 
VAR Tot_1_Day_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -1, DAY ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_3_Day_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -3, DAY ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_7_Day_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -7, DAY ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_28_Day_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -28, DAY ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_1_Month_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -1, MONTH ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_3_Month_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -3, MONTH ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_6_Month_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -6, MONTH ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
VAR Tot_1_Year_P_Rev =
    SUMX (
        CALCULATETABLE (
            'CTL CommPay',
            DATEADD ( Dates[Date], -1, YEAR ),
            ALL ( 'CTL CommPay'[PaymentPeriod] )
        ),
        'CTL CommPay'[AmountGross]
    )
RETURN
    ( Tot_1_Day_P_Rev + Tot_3_Day_P_Rev + Tot_7_Day_P_Rev + Tot_28_Day_P_Rev + Tot_1_Month_P_Rev + Tot_3_Month_P_Rev + Tot_6_Month_P_Rev + Tot_1_Year_P_Rev )
        / (
            IF ( ISBLANK ( Tot_1_Day_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_3_Day_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_7_Day_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_28_Day_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_1_Month_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_3_Month_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_6_Month_P_Rev ), 0, 1 )
                + IF ( ISBLANK ( Tot_1_Year_P_Rev ), 0, 1 )
        )

Summarized Forecast =
SUMX (
SUMMARIZE ( Dates, Dates[Date], “Summ Forecast”, [Forecast Calc] ),
[Summ Forecast]
)

Forecast =
SWITCH (
TRUE (),
ISBLANK ( [Total Gross CommPay RR] ) = TRUE (), [Summarized Forecast],
TRUE (), [Forecast Calc]
)

Total Gross CommPay RR = 
SUM ( 'CTL CommPay'[AmountGross] )

Thanks

Hi @adsa, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • Your current work-in-progress PBIX file
  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

Attached PBIX & Excel Sample Files.

Incorrect overall grand total

Forecast.pbix (1.8 MB)
Sample Data.xlsx (2.9 MB)

Hi @adsa,

Q: In your sample you are bringing in monthly data, is that also the case in your production model?
If so, please be aware of the fact that you can’t shift over a lower granularity without an additional allocation measure.

image

This affects your Avg calc so I removed that from FC Calc v2, below. Because it’s quite an extensive formula already I created a separate display measure where I correct the total by iterating over the Full FY and Month Name, when the Month Name isn’t INSCOPE.

Here’s your sample file.
Forecast.pbix (1.8 MB)

You can find more information and links to relevant content on fixing totals here:

I hope this is helpful.

4 Likes

@Melissa Thank you so much for this.

Took me a little longer to digest what you have done but can finally understand it!

1 Like