Week over Week Sales Growth

[Jos13]

Hi Team,

I have a data set in the following format.

I have created a measure to calculate daily growth as follows

Daily growth% =

    VAR __PREV_day = CALCULATE([Sales], DATEADD('DateTable'[Date], -1, DAY))

    RETURN

        DIVIDE([Sales] - __PREV_day, __PREV_day)

I am not able to find weekly growth using the above method since week is not supported in DATEADD. Please help me on this.

Best Regards,

Jos

[v-shex-msft · Accepted solution]

HI @Jos13,

You can try to use following link if it meets your requirement:

Weekly growth% =
VAR currDate =
    MAX ( 'DateTable'[Date] )
VAR _curr =
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && WEEKNUM ( [Date] ) = WEEKNUM ( currDate )
        )
    )
VAR _prev =
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            IF (
                WEEKNUM ( currDate ) > 1,
                YEAR ( [Date] ) = YEAR ( currDate )
                    && WEEKNUM ( [Date] ) = WEEKNUM ( currDate ),
                YEAR ( [Date] )
                    = YEAR ( currDate ) - 1
                    && WEEKNUM ( [Date] )
                        = WEEKNUM ( DATE ( YEAR ( currDate ) - 1, 12, 31 ) )
            )
        )
    )
RETURN
    DIVIDE ( _curr - _prev, _prev )

Regards,

Xiaoxin Sheng

[AntrikshSharma]

Use the date template from SQLBI https://www.sqlbi.com/tools/dax-date-template/ it has week columns and then you can use FILTER ALL construct to do calculations over weeks.

[amitchandak]

@Jos13 , Try if this can help

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[Mariusz]

Hi @Jos13 

You can use the Relative Date Dimension table or parts of it, the below blog post will have an example of the previous week

https://community.powerbi.com/t5/Community-Blog/Relative-Date-Dimension/ba-p/779039

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn