Dynamically return number of months remaining in your Financial Year Calendar

[Axiomite]

Hi, 

Busy with a forcast calculation that looks as follows:

Forecast Actual Amount IS = 
VAR Rolling12Mx4 = [Act Amount R12M] * [Months_until_EOFYC]
VAR CurrentActFYTD = [Current Actual YTD Fiscal]
RETURN
CurrentActFYTD + Rolling12Mx4

However, the problem is the [Months_until_EOFYC] (end of calendar financial year) which is calculated as follows, it remains fixed and returns the current months Financial year number and doesn not dynamically change with my Dimdate[Date] slicer.

Months_until_EOFYC = 12-[CurrentFiscMonthNo] 

CurrentFiscMonthNo = SWITCH([CurrentCalMonthNo],
    1, 11,
    2, 12,
    3, 1,
    4, 2,
    5, 3,
    6, 4,
    7, 5,
    8, 6,
    9, 7,
    10, 8,
    11, 9,
    12, 10
)

CurrentCalMonthNo = MONTH(TODAY())

The slicer is taken from my dimdates table linke to the [Txdate] in the factTable:

dimDates = 
VAR BaseCalendar = 
    CALENDAR(DATE(2019,03,01),DATE(YEAR(TODAY()+365), 03, 01))
RETURN
    GENERATE
    (
        BaseCalendar,
        VAR BaseDate = [Date]        
        VAR YearDate = YEAR(BaseDate)
        VAR Quarter = QUARTER(BaseDate)
        VAR MonthNr = FORMAT(BaseDate,"MM")
        VAR MonthNrs = FORMAT(BaseDate,"MM")
        VAR Month = FORMAT(BaseDate, "MMM")
        VAR Week = WEEKNUM(BaseDate)
        VAR Day = DAY(BaseDate)
        VAR FiscalYear = IF(MONTH(BaseDate)>=3,YEAR(BaseDate),YEAR(BaseDate)-1)
        VAR FiscalMonthNr = FORMAT(DATE(YEAR(BaseDate),IF(MONTH(BaseDate)>=3,MONTH(BaseDate)-2,MONTH(BaseDate)+10),1),"MM")

        VAR FiscalMonth = FORMAT(DATE(FiscalYear, FiscalMonthNr, 1), "MM")
        RETURN ROW
        (
            "Year", YearDate,
            "Quarter", Quarter,
            "Month Year", Month & " " & YearDate,
            "YearMonth", YearDate & MonthNrs,
            "MonthNr", MonthNr,
            "Month", Month,
            "Fiscal Year Month", FiscalYear & "-" & FiscalMonth,
            "FiscalMonthnr", FiscalMonthNr,
            "Week", Week,
            "Day", Day
            
        )
    )

I've been going around in circles, the following seems to interact with the slicer and the result in correct format however starting by selecting the slicer from the first Month of the Financial Calender March it returns 31, then April 60, May 93 :

FiscalMonthNum = 
if(MONTH(dimDates[Date])>=3,MONTH(dimDates[Date])-2,MONTH(dimDates[Date])+10)

Any suggestions and if possible a quick and easy rather than re-doing the enitre report that is already connected to my dimdates table? 

Kind Regards

[BiNavPete]

HI @Axiomite 

Could you add a column into your date table?
Within the GENERATE add another column
VAR FiscalMonthsToGo = 12 - FiscalMonthNr

Then in the Return statement add that column in

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Happyt to help!!

Pete
Linked In: https://www.linkedin.com/in/pete-smith-955b73181

[Axiomite]

Thanks @BiNavPete , I went straight to the reply from @johnt75 and gave me the correct result, nevertheles, thanks for your imput and will investigate your solution as soon as I have time. Kind Regards

[johnt75 · Accepted solution]

I think you can just change the calculation for the current month number, 

CurrentCalMonthNo = MONTH( MAX(dimDates[Date]) )

[Axiomite]

Hi @johnt75 , many thanks!