Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
I think you can just change the calculation for the current month number,
CurrentCalMonthNo = MONTH( MAX(dimDates[Date]) )
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
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
I think you can just change the calculation for the current month number,
CurrentCalMonthNo = MONTH( MAX(dimDates[Date]) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |