Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jnet
Frequent Visitor

Rolling last 13 week average % [not working]

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
jnet_0-1668788199093.png

 

Thank you.

5 REPLIES 5
v-xiaotang
Community Support
Community Support

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
Super User
Super User

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
Frequent Visitor

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_0-1668790914812.pngjnet_1-1668790948093.png

jnet
Frequent Visitor

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

jnet_0-1668790234511.png

 

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

            )

     )

)

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.