Weekly on Week Sales Variance formular

[Rabz0912]

Hi Guyz,

Please i need a dax formular for Week on Week Sales Varince. Any help will be so much appreciated 

[Fowmy · Accepted solution]

@Rabz0912 

Make sure you have a dates table with a week number column

WeekOverWeek =
VAR LW =
    CALCULATE (
        SUM ( table[sales] ),
        FILTER (
            ALL ( 'dates' ),
            'dates'[weeknumber]
                = MIN ( 'dates'[weeknumber] ) - 1
        )
    )
RETURN
    SUM ( Append1[Video Views] ) - LW

[v-luwang-msft · Accepted solution]

Hi @Rabz0912 ,

Since you did not provide specific data, I will use the following data as a template:

Step 1,create date table :

date = CALENDAR("2021,1,1","2021,12,31")

use the below to create new column:

weeknum = WEEKNUM('date'[Date]) 

And if you want weeknum start from  2021.1.1,use the below:

weeknum = IF( WEEKNUM('date'[Date]-5) >20&&MONTH('date'[Date])=1,1,WEEKNUM('date'[Date]-5))

Step2 ,create relationship:

Sales Varince = 
CALCULATE (
    SUM ( 'Table'[sale] ),
    FILTER ( ALL ( 'date' ), 'date'[weeknum] = MAX ( 'date'[weeknum] ) )
)
    - CALCULATE (
        SUM ( 'Table'[sale] ),
        FILTER ( ALL ( 'date' ), 'date'[weeknum] = MAX ( 'date'[weeknum] ) - 1 )
    )

And the following article may be helpful for you!

Variances: YoY, monthly and weekly comparision etc

Wish it is hlepful for you!

Best Regards

Lucien

[Fowmy · Accepted solution]

@Rabz0912 

Make sure you have a dates table with a week number column

WeekOverWeek =
VAR LW =
    CALCULATE (
        SUM ( table[sales] ),
        FILTER (
            ALL ( 'dates' ),
            'dates'[weeknumber]
                = MIN ( 'dates'[weeknumber] ) - 1
        )
    )
RETURN
    SUM ( Append1[Video Views] ) - LW