Forum Discussion
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"))
Solution SageI 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,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
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"))
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!
- 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.