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
Filter ->  3  (coming from a filter table)


FC                Rolling FC   Rolling FC (Bias)
M1 10           10
M2 15           25
M3 20           45
M4 23           68                68
M5 15            83               83
M6 20            103             103
M7 23           126             126

Measure 1 Rolling FC
Measure 2 Rolling FC (Bias)

 

Any help/ direction would be highly appreciated. 

 

Thanks,

mymsr

 

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)

     

19 Replies

  • johnyip's avatar
    johnyip
    Solution Sage
    3 years ago

    mysmsr , from your sample, maybe your biased FC's measure is as simple as follows:

     

     

    Rolling FC (Bias) = IF(MID(MAX([FC]),2,LEN(MAX([FC])))=ALLSELECTED([Your filter]),BLANK(),[Rolling FC])

     

    Meanwhile, if you can provide your sample data, the answer will be more guaranteed to be correct.

  • mysmsr's avatar
    mysmsr
    Frequent Visitor
    3 years ago

    Hello johnyip,

     

    Thanks for the prompt reply. my mistake i should below screen print. Month column is in dateformat from calendar table. not sure how I can share data from this platform.

     

     

    Regards,

    Raghu

     

  • mysmsr's avatar
    mysmsr
    Frequent Visitor
    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
      Solution Sage
      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)

       

      • mysmsr's avatar
        mysmsr
        Frequent Visitor
        3 years ago

        I have one slicer for the BIAS value from 'Bias table'[Bias]. Other than that I have used filter on page to get last 14 months. Based on Fiscal Year table (FY table) month column.

         

         

         

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