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)

     

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

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

       

      • johnyip's avatar
        johnyip
        Solution Sage
        3 years ago

        Don't have the sample data and cannot test that. Please see if this works.

         

         

        YTD Actuals =
VAR MaxMonth = MAXX(ALLSELECTED('FY Table'),[Month])
VAR VirtualTable = SUMMARIZE(ALLSELECTED(FY Table),
                             [Month],
                             "Rolling Actual",[Rolling Actuals]
                   )
VAR Rolling_Actuals = CONCATENATEX(VirtualTable,[Rolling Actual],"|")
VAR Last_Rolling_Actual = PATHITEM(Rolling_Actuals,PATHLENGTH(Rolling_Actuals))
VAR Result = IF(MAX([Month]) <> BLANK(), Last_Rolling_Actual,BLANK())
RETURN
Result