Calculating Forecast for Next 3 months at year change.

[Anonymous]

Hello all,

I am developing a forecasting report with forecast excels which shows next 3 months all time.

Report need to divide current year and previous years YTD Values.

I have done some measures to calculate Year-to-Date calculations using TOTALYTD and Sameperiodlastyear.

The report need to show next 3 month all time. I attached the example of current forecast. (October 21, November 21, December21)

It works fine as attached.

The problem starts here, Next month the report need to show next 3 months as mentioned but this year is ending and I have fixed the year to " This Year" from advanced filter on all pages.

The report need to show values as below. November 21 YTD , December21 YTD and January 22 YTD
(january  ytd will be equal to january)

But I struggled a lot to calculate next 3 months for November 21 December 21 and January 22.

so the "2021 % values" need to divide 2021 values to 2020 values. and "2022 % values" need to divide 2022 January value to 2021 January value

Here is my Previous measures that working fine now.

CURRENT_Year_YTD = CALCULATE(TOTALYTD(SUM('FactForecast'[Value']),Dates[Date]))
Previous_Year_YTD = CALCULATE(TOTALYTD(SUM('FactForecast'[Value']),SAMEPERIODLASTYEAR(Dates[Date])))
Percent % = DIVIDE([CURRENT_Year_YTD],[Previous_Year_YTD])

I hope I explained myself. I need to solve this as soon as possible. So feel free to ask me anything.

Thanks for your time.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous 

In your fact table, do you have data up to January 2022? If so, please try the following measure:

Measur1e =
VAR _startdate =
    SELECTEDVALUE( Dates[Date] )
VAR _current_year =
    CALCULATE(
        SUM( 'FactForecast'[Value] ),
        FILTER(
            ALL( 'FactForecast' ),
            [Business] = SELECTEDVALUE( 'FactForecast'[Value] )
                && YEAR( [Date] ) = YEAR( _startdate )
                && MONTH( [Date] ) = MONTH( _startdate )
        )
    )
VAR _previous_year =
    CALCULATE(
        SUM( 'FactForecast'[Value] ),
        FILTER(
            ALL( 'FactForecast' ),
            [Business] = SELECTEDVALUE( 'FactForecast'[Value] )
                && YEAR( [Date] )
                    = YEAR( _startdate ) - 1
                && MONTH( [Date] ) = MONTH( _startdate )
        )
    )
RETURN
    DIVIDE( _current_year, _previous )

Please provide some example date, so that I can give better results.

Best Regards

Community Support Team _ chenwu zhu

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

[Anonymous]

Hi ,

I reviewed your dax and it it feels might be the solution. But I dont have 2022 data avaible at my fact table.

I will make a dummy data for your measure. 

Can you explain the Dax I attached ?  and how to implement this solution in my current matrix visual ? 

FILTER(
            ALL( 'FactForecast' ),
            [Business] = SELECTEDVALUE( 'FactForecast'[Value] )
                && YEAR( [Date] ) = YEAR( _startdate )
                && MONTH( [Date] ) = MONTH( _startdate )

 Thank you so much!

Take care.

[v-chenwuz-msft]

Hi,

A little difficult to explain,the dax you attacged means return a filtered FactForecast table, where the "business" field = current business and year = startdate's year and month = startdate's month.

Have I explained it clearly?