Forum Discussion

mysmsr's avatar
mysmsr
Frequent Visitor
3 years ago
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...
Need Help
  • mysmsr's avatar
    mysmsr
    3 years ago

    johnyip  I managed to create the code as below. It's working fine for the year 22 but stops when  the year changes.

     

    Rolling Forecast Bias =
                            IF(
                                MONTH(
                                    MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1,[Rolling Forecast (13 WK)],0
                            )

     

     

  • johnyip's avatar
    johnyip
    3 years ago

    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)

     

  • johnyip's avatar
    johnyip
    3 years ago

    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)

     

mysmsr's avatar
mysmsr
Frequent Visitor
3 years ago

Sure. Bias table is just a parameter table where I have  stored values of Bias and used it in the slicer to change the bias calculation dynamically in report. 

I have created a Fiscal year calendar table at month level using my fact table dates (IBP Accuracy Data).

In the fact table I have my FC and Actuals stored at Monthly level.

 

 

johnyip's avatar
johnyip
Solution Sage
3 years ago

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)

 

 

 

  • mysmsr's avatar
    mysmsr
    Frequent Visitor
    3 years ago

    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..

     

    • johnyip's avatar
      johnyip
      Solution Sage
      3 years ago

      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)

       

      • mysmsr's avatar
        mysmsr
        Frequent Visitor
        3 years ago

        Excellent!!!. That worked. Thanks a lot.