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)

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi mysmsr ,

 

Please try:

Rolling FC (Bias) =
CALCULATE (
    [Rolling FC],
    OFFSET (
        -3,
        ALLSELECTED ( 'FY Table'[Month] ),
        ORDERBY ( 'FY Table'[Month], ASC )
    )
)

 

OFFSET

 

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

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