Weekly measures returning blanks!

[Anonymous]

Hi there, 

I'm hoping someone might be able to help me!

I'm trying to create weekly measures (using daily data), and they keep returning blank values. I've tried so many different versions of the calculation!

I've created a dummy file (below), which includes my measures.

https://www.dropbox.com/s/85v0wkkw4ez7w9d/Example%20file.pbix?dl=0 

Does anyone have any ideas?

Thank you!

Sonia

[v-gizhi-msft]

Hi,

According to your description, please try these measures:

Average engagement rate for last week =

AVERAGEX(SUMMARIZE (

    'DateKey','DateKey'[Date],

    "T", IF (

        SELECTEDVALUE ( 'DateKey'[Week #] ) = 1,

        AVERAGEX (

            FILTER (

                ALL ( 'DateKey' ),

                'DateKey'[Week #] = CALCULATE ( MAX ( 'DateKey'[Week #] ), ALL ( 'DateKey' ) )

                    && 'DateKey'[Year]

                        = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1

            ),

            [Ave Eng Rate]

        ),

        AVERAGEX (

            FILTER (

                ALL ( 'DateKey' ),

                'DateKey'[Week #]

                    = SELECTEDVALUE ( 'DateKey'[Week #] ) - 1

                    && 'DateKey'[Year] = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) )

            ),

            [Ave Eng Rate]

        )

    )

),[T])

Average engagement rate for the week prior to last week =

AVERAGEX(SUMMARIZE (

    'DateKey','DateKey'[Date],

    "T",

IF (

    SELECTEDVALUE ( 'DateKey'[Week #] ) = 1,

    AVERAGEX (

        FILTER (

            ALL ( 'DateKey' ),

            'DateKey'[Week #]

                = CALCULATE ( MAX ( 'DateKey'[Week #] ) - 1, ALL ( 'DateKey' ) )

                && 'DateKey'[Year]

                    = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1

        ),

        [Ave Eng Rate]

    ),

    IF (

        SELECTEDVALUE ( 'DateKey'[Week #] ) = 2,

        AVERAGEX (

            FILTER (

                ALL ( 'DateKey' ),

                'DateKey'[Week #] = CALCULATE ( MAX ( 'DateKey'[Week #] ), ALL ( 'DateKey' ) )

                    && 'DateKey'[Year]

                        = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) ) - 1

           ),

            [Ave Eng Rate]

        ),

        AVERAGEX (

            FILTER (

                ALL ( 'DateKey' ),

                'DateKey'[Week #]

                    = SELECTEDVALUE ( 'DateKey'[Week #] ) - 2

                    && 'DateKey'[Year] = VALUE ( SELECTEDVALUE ( 'DateKey'[Year] ) )

            ),

            [Ave Eng Rate]

        )

    )

)),[T])

Average weekly engagement rate YTD =

AVERAGEX(SUMMARIZE(DateKey,'DateKey'[Date],"T",TOTALYTD([Ave Eng Rate],DateKey[Date])),[T])

Note that the [Last Calendar Week Engagement] measure is still blank because [WEEK NUM] column in table ‘DATA’ starts from 34 and has no value of 1.

If you want to change it, it is the same with the above:

Last Calendar Week Engagement =

AVERAGEX(SUMMARIZE('DateKey','DateKey'[Date],"T",

CALCULATE(

    [Total Engagement],

    'DateKey'[Week #] = WEEKNUM(TODAY(),1) -1

)),[T])

And the result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[Anonymous]

Hi @v-gizhi-msft , 

Thank you for your help!

This solves the 'blank' issue, but the numbers aren't looking quite right... 

The 'average for last week' measure doesn't match when i check it in a table against the 'eng rate' metric, averaged by week: 

https://www.dropbox.com/s/1vlhf94dgeeinvf/Case-Weekly%20measures%20returning%20blanks%21.pbix?dl=0 

Thanks again, 

Sonia

[v-gizhi-msft · Accepted solution]

Hi,

Please try to use this measure to show Average engagement for this week:

Average engagement rate for this week =

AVERAGEX (

    SUMMARIZE (

        DateKey,

        'DateKey'[Date],

        "T", AVERAGEX (

            FILTER (

                ALLSELECTED ( DateKey ),

                'DateKey'[Week #] = SELECTEDVALUE ( 'DATA'[WEEK NUM] )

            ),

            [Ave Eng Rate]

        )

    ),

    [T]

)

And the result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[amitchandak]

I changed Last week data like

Last Calendar Week Engagement = 
var _weeknum = if(max(DATA[WEEK NUM])=1,52,max(DATA[WEEK NUM]))
var _year = if(MAX(DATA[YEAR])=1,MAX(DateKey[Year])-1,MAX(DATA[YEAR]))
Return
CALCULATE(
    [Total Engagement],
    FILTER(DateKey,DateKey[Week #]=_weeknum && DateKey[Year] =_year)
)

Check if this is correct. Others can be changed in the same manner.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi there @amitchandak , 

Thank you so much for your help.

Your measure works perfectly on the example file, but for some reason when I use it in my real file, I'm getting blank again! 

Have you any idea why this might be happening? 

I've double checked all my number formats, and they're all the same as in the example file... 

Thank you again, so much!

Sonia

[Anonymous]

Hi there @amitchandak , 

I think I know why it's returning blank! I think it's getting confused with week 53/ week 1 in w/c 30th Dec is throwing it out. 

Do you know how to correct this? 

Also, I'm hoping you might be able to help me out with the formula for 'week prior to last', and also 'average weekly YTD'??

Thanks again, 

Sonia