Forum Discussion
Solved
Dax help
Hello experts, Need help to write a new measure using DAX to calculate Rolling FC (Bias) where value gets calculated from a seleted month in the filter. Ex: bias month in this case is 3. Bias F...
Do you have a slicer to do the filtering? If so, what is the field you have used?
By reading the screencap of your calender table, maybe you can try the following:
Rolling Forecast Bias =IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,{{{ADD THE LOGIC THAT THE DATA YEAR IS LARGER THAN SELECTEDYEAR HERE}}}),[Rolling Forecast (13 WK)],0)mysmsr , perhaps you can try to see if this works.
Rolling Forecast Bias = VAR VirtualTable = SUMMARIZE(ALLSELECTED(FY Table), "Year",YEAR('FY Table'[Month]) ) VAR MinYear = VALUE(MINX(VirtualTable,[Year])) RETURN IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,YEAR(MAX('FY Table'[Month])) >= MinYear),[Rolling Forecast (13 WK)],0)
I think the most simple way is you can create another parameter table to store the value of [year], and then use the following DAX:
Rolling Forecast Bias =IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,YEAR(MAX('FY Table'[Month])) >= SELECTEDVALUE('Year parameter table'[Year])),[Rolling Forecast (13 WK)],0)
Or, in response to your "filter on page to get last 14 months", the following might work (you need to test if there is an error since I haven't). No parameter table / slicer of year is needed for the following:
Rolling Forecast Bias =
VAR VirtualTable = SUMMARIZE(ALLSELECTED(FY Table),
[Month],
"Year",MIN(YEAR('FY Table'[Month]))
)
VAR MinYear = MINX(VirtualTable,[Year])
RETURN
IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,YEAR(MAX('FY Table'[Month])) >= MinYear),[Rolling Forecast (13 WK)],0)
Thanks. Was trying Virtual table option.
VAR Vtable = SUMMARIZE(ALLSELECTED('FY Table'),[Month],
"Year",MIN(YEAR('FY Table'[Month]))
)
VAR MinYear = MINX(Vtable,[Year])
return
IF(OR(MONTH(MAX('FY Table'[Month]))>= SELECTEDVALUE('Bias table'[Bias])+1,
YEAR(MAX('FY Table'[Month]))>=MinYear),[Rolling Forecast (13 WK)],0)
getting error for the las column.
The MIN function only accepts a column reference as an argument.
Couldn't figureout any issue. Could you pl check..
mysmsr , perhaps you can try to see if this works.
Rolling Forecast Bias = VAR VirtualTable = SUMMARIZE(ALLSELECTED(FY Table), "Year",YEAR('FY Table'[Month]) ) VAR MinYear = VALUE(MINX(VirtualTable,[Year])) RETURN IF(OR(MONTH(MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,YEAR(MAX('FY Table'[Month])) >= MinYear),[Rolling Forecast (13 WK)],0)