cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

YTD Measure using Forecast for uncompleted month

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

Re: YTD Measure using Forecast for uncompleted month

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"))

 

Capture.PNG

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

7 REPLIES 7
Highlighted
Solution Sage
Solution Sage

Re: YTD Measure using Forecast for uncompleted month

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 () )
        )
    )

 

Highlighted
Responsive Resident
Responsive Resident

Re: YTD Measure using Forecast for uncompleted month

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

 

 

 

 

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
Highlighted
Frequent Visitor

Re: YTD Measure using Forecast for uncompleted month

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.

 

 

 

Highlighted
Responsive Resident
Responsive Resident

Re: YTD Measure using Forecast for uncompleted month

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"))

 

Capture.PNG

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

Highlighted
Frequent Visitor

Re: YTD Measure using Forecast for uncompleted month

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!

Highlighted
Responsive Resident
Responsive Resident

Re: YTD Measure using Forecast for uncompleted month

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.

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
Highlighted
Anonymous
Not applicable

Re: YTD Measure using Forecast for uncompleted month

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors