Forum Discussion
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?
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"))
7 Replies
mattbriceSolution Sage
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 () ) ) )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,000The 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
Continued Contributor
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"))
- 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.
CheersGreg
