YTD Measure using Forecast for uncompleted month

[kevin121519]

This has been an interesting and challenging measure to create. I am looking to create a measure that calculates the YTD sales up to the last completed month. For any month that has yet to be completed I would like to use the forecast for that month to give a sort of "Latest Estimate" measure. The challenge here is this measure would be dynamic and change with each completed month. Also, the data is refreshed daily so the current month would need to be excluded and replaced with the forecast. How would one go about creating this measure?

[dearwatson]

My approach here is to always build the basic root measures first then use those measures in the more complex problem:

(I am presuming you have a Calendar table.)

Lets do basic YTD sales and YTD Forecast Sales first

Total Sales = SUM(Table[Sales])

YTD Sales = CALCULATE([Total Sales},DATESYTD(Calendar[Date]))

Forecast Sales = SUM(Table2[Forcast])

YTD Forecast = CALCULATE([Forcast Sales},DATESYTD(Calendar[Date]))

Work out the current month - this will only work if you dont have any future dates in your sales table

Current Month = CALCULATE(MONTH(MAX(Table[DateKey]),ALL(Table))

Test for current month and if its true then use Forecast not Sales...

Actual/Forcast = IF(MONTH(MAX(Table[DateKey]))=[Current Month],[Forcast YTD],[Sales YTD])

kinda works... Hope this helps.

Cheers

Greg

[mattbrice]

I have done something similar.  I had a summary calendar table with year, month name and month number columns.  The table is not related to any other.  Then put the fields in the report as a slicer for the user to pick a month& year. Then the measures are not that hard:

YTD Revenue =
VAR PeriodSelected =
    IF (
        HASONEVALUE ( DisconnectedCalendar[MonthNumber] ),
        VALUES ( DisconnectedCalendar[MonthNumber] )
    )
VAR YearSelected =
    IF (
        HASONEVALUE ( DisconnectedCalendar[Year] ),
        VALUES ( DisconnectedCalendar[Year] )
    )
RETURN
    CALCULATE (
        SUM ( Table[Revenue] ),
        FILTER (
            ALL ( Calendar ),
            Calendar[MonthNumber] < PeriodSelected
                && Calendar[Year] = YearSelected
        )
    )
        + CALCULATE (
            SUM ( Table[Forecast] ),
            FILTER (
                ALL ( Calendar ),
                Calendar[MonthNumber] = PeriodSelected
                    && Calendar[Year] = YearSelected
            )
        )

 or if you want to just have it be relative to "Today", then you could do away with disconnected calendar and just do:

YTD Revenue =
CALCULATE (
    SUM ( Table[Revenue] ),
    FILTER (
        ALL ( Calendar ),
        Calendar[MonthNumber] < MONTH ( TODAY () )
            && Calendar[Year] = YEAR ( TODAY () )
    )
)
    + CALCULATE (
        SUM ( Table[Forecast] ),
        FILTER (
            ALL ( Calendar ),
            Calendar[MonthNumber] = MONTH ( TODAY () )
                && Calendar[Year] = YEAR ( TODAY () )
        )
    )

[kevin121519]

To clarify a little further. The second solution works for a total value but does not connect to the Fiscal Period (ie does not show the running total for the entire month). Here is some sample data to help clarify things. Another problem is our fiscal year runs from Dec-Nov. 

Fiscal Period                          Sales Amount             Latest Estimate              What I would like
December 2016                     $10,000,000                                                     $10,000,000
January 2017                         $11,000,000                                                     $11,000,000
February 2017                       $12,000,000                                                     $12,000,000
March 2017                           $13,000,000                                                     $13,000,000
April 2017                              $2,000,000                  $14,000,000                 $14,000,000
May 2017                                                                  $15,000,000                 $15,000,000
June 2017                                                                 $16,000,000                  $16,000,000
July 2017                                                                   $17,000,000                 $17,000,000
August 2017                                                             $18,000,000                  $18,000,000
September 2017                                                       $19,000,000                  $19,000,000
October 2017                                                           $20,000,000                  $20,000,000
November 2017                                                       $21,000,000                  $21,000,000

The issue is in April 2017 I would like to see the Sale Amount ignored and the Latest Estimate used. From there I believe calculating a YTD running total should be fairly easy.

[dearwatson · Accepted solution]

OK I built something that looks to work... proabably could be optimised:

Base Measures:

Current Month = MONTH(TODAY())

Current Year = YEAR(TODAY())

FP Month = MONTH(MAX(Table1[Fiscal Period]))

FP Year = YEAR(MAX(Table1[Fiscal Period]))

Total Estimate = SUM(Table1[Estimate])

Total Sales = SUM(Table1[Sales])

Outputs

Output = IF([FP Month]<[Current Month]||[FP Year]<[Current Year],[Total Sales],[Total Estimate])

I needed to SUMX these for the YTD to work properly - note the incorrect Total of Output 1 in the pic

Output2 = SUMX(Table1,[Output])

FPYTD = CALCULATE([Output2],DATESYTD(Table1[Fiscal Period],"11/30"))

[kevin121519]

This is semi working for me and I think it is very very close to getting the desired results. The last issue I am running into is that for Output2 = SUMX(Table1,[Output]), the estimate is in another table than the sales. This is resulting in FPYTD showing correctly for the first four months but incorrect for the remaining months as I am getting the Grand total shown for those months.

Is there a work around for this? 

Appreciate all your help!

[Anonymous]

Hi Kevin,

I have the similar issue and seems to be stuck at the point where you have almost mentioned. I would kie to know how you did it and was you able to fix the issue you faced. I am having a Cumalative YTD value and YTD +FC value which changes based on month selection. Please refer the below link where I have posted my query and l will know if you can help me on this
Link for Original Question

[dearwatson]

Hmm, so budget is not getting sliced by the date? (this is my guess)

You may need a seperate Calendar table that links to both the budget and actual tables by date key

then use the calendar date as the date in your YTD calc... that should work.