Rolling last 13 week average % [not working]

[jnet]

Measure:

L13W FA KG = AVERAGEX(DATESBETWEEN('TM1 Facts'[Wk Start],DATEADD(Baseline[Last Date],-91,DAY),DATEADD(Baseline[Last Date],1,DAY)),CALCULATE(Baseline[FA KG]))

Example Data: L13W FA KG % (3rd column) does not display last 13 week average of FA KG % (first column), as desired

Thank you.

[v-xiaotang]

Hi @jnet 

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

the first argument of DATESBETWEEN and DATEADD shall be a Date column. Try this:

L13W FA KG = 

CALCULATE(

    AVERAGEX(

        'TM1 Facts'[Wk Start],

        CALCULATE(Baseline[FA KG])   

         ),

    DATESBETWEEN(

        'Date'[Date],

        DATEADD(

             Date'[Date],

             -91,

             DAY

         ),

        DATEADD(

             Date'[Date],

              1,

              DAY

            )

     )

)

[jnet]

Copying above suggestion, I got below error so I tried changing AverageX to Average and Baseline[FA KG] from measure to column to get a result... but it still looks incorrect. 

[jnet]

Thanks. This is the error message I get when taking above suggestion:

[FreemanZ]

indeed the column needs to be converted to a table, try this:

L13W FA KG = 

CALCULATE(

    AVERAGEX(

        VALUES('TM1 Facts'[Wk Start]),

        CALCULATE(Baseline[FA KG])   

         ),

    DATESBETWEEN(

        'Date'[Date],

        DATEADD(

             Date'[Date],

             -91,

             DAY

         ),

        DATEADD(

             Date'[Date],

              1,

              DAY

            )

     )

)