Forum Discussion

kevin121519's avatar
kevin121519
Frequent Visitor
9 years ago
Solved

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 complet...
Tips and Tricks
  • dearwatson's avatar
    dearwatson
    9 years ago

    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's avatar
kevin121519
Frequent Visitor
9 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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