Forum Discussion

Chedva's avatar
Chedva
Helper II
6 years ago
Solved

Current month forecast

Hi, I have a consolidated forecast table that has SF month - the month the forecast was done, month - the month of the forecast & forecats QTY.   Ex;  SF month - Jan 2020 , Month - Dec 2020,  - t...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Perhaps:

     

    Measure = 
  VAR __Forecast = CALCULATE(sum('Forecast Consol'[Forecast QTY]),USERELATIONSHIP('Forecast Consol'[SF Month],'Dates Slicer'[DateKey])))
  VAR __LastMonth = <calculate what your last month's date is, not entirely sure how to do this with the information provided. If you had an actual date field, this would be relatively easy>
RETURN
  IF(
    ISBLANK(__Forecast),
    CALCULATE(sum('Forecast Consol'[Forecast QTY]),'Forecast Consol'[SF Month]=__LastMonth),
    __Forecast
  )
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

See, not having an actual date here is problematic. Do you have an actual date or is your "Month" column really text that says "March 2020"? If that is really the case, you *might* be able to get away with something like:

 

last month = FORMAT(EOMONTH(DATEVALUE(MAX([Month]),-1),"mmmm yyyy")

 

Maybe, in theory that takes the maximum value of the Month column in the current context, converts it to a date (DATEVALUE), subtracts a month (EOMONTH) and then FORMAT puts the resulting date in mmmm yyyy format. (February 2020 for example)

Chedva's avatar
Chedva
Helper II
6 years ago

Month is an actual Month so  EOMONTH(today(),-1) worked 🙂

 

There is two more thing I need to fix in this solution, 

1. Remove the forecast for a month before the SF month. I'll explain, when I take SF March 2020 I have Month - March 2020.

When I run this measure and chose April 2020 and I don't have the updated forecast It wil give me March's - great, But I want to remove the line SF month - April 2020 and Month - March 2020. I only want to see the forecast for upcoming months. so for April starting from April. 

2. When I chose April, and it gives me march's forecast because that is the ltest updated it shows me SF month - April. I want it to stil show March 2020 for those that the forecast wasn't updates - not sure if this is possible - lees important.

 

Thanks Greg_Deckler 

Chedva

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    OK, I'm not following this 100%. Is there any way you could provide sample data and expected result from sample data?