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

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
Icon for Solution Sage rankSolution 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.

     

     

     

    • johnyip's avatar
      johnyip
      Icon for Solution Sage rankSolution Sage
      3 years ago

      How is your FY Table look like?

  • mysmsr's avatar
    mysmsr
    Frequent Visitor
    3 years ago

    Hello johnyip,

     

    Just wandering if you can help me to fix below issue please..
     I have written below dax to get last month rolling actuals which is 15598152 for ytd. It works when I use card visual. But when I place it in matrix it changes. 

    YTD Actuals =
            CALCULATE(
                [Rolling Actuals],
                YEAR('FY Table'[Month]) = YEAR(MAX('FY Table'[Month])) &&
            MONTH('FY Table'[Month]) = MONTH(MAX('FY Table'[Month]))
    )

     And hence my graph gets wrong data when I plot YTD accuracy w.r.t month.

     

     

     

    thanks.

    msr

     

     

    • johnyip's avatar
      johnyip
      Icon for Solution Sage rankSolution Sage
      3 years ago

      cannot quite understand. what is  your expected number of YTD actuals?

      • mysmsr's avatar
        mysmsr
        Frequent Visitor
        3 years ago

        Sorry. My ytd actual should be a constant value of last(max) month rolling actuals which is 15598152. This value should be the same across all months. Hence I tried to capture MAX of rolling actuals in my dax code.