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

v-cgao-msft, 

Thanks for your reply. I am not looking for offset value. I need to populate the same value in Rolling FC after few months based on the selected value from BIAS table. 
I understand the current code issue. Since we are using Month() funtion it is returing month number which is independent of FY year and our condition MAX('FY Table'[Month])) >= SELECTEDVALUE('Bias table'[Bias])+1 will not work.

Regards,

Raghu

 

johnyip's avatar
johnyip
Solution Sage
3 years ago

Can I have a look of the structure and some sample data of Bias table?

 

Need to udnerstand why you wrote SELECTEDVALUE('Bias table'[Bias]), or more from your data structure to come up with the correct DAX.

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