Forum Discussion

great_AS's avatar
great_AS
Frequent Visitor
3 years ago
Solved

WoW sales variation

Hello,

 

I can't get the WoW variation, can you please help me out?

 

I have an excel with:

Year: 2019-2022
Week: 1-52
KPIs: sales, profit revenue
Countries: yyyyy

 

First I need to create a table where I have Countries in the rows and I have to display the following:

  1. KPIs (absolute) last week (which is 49 and not 50 in my case as we get data one week delayed), one column for each KPI
  2. Variation (%) against last week-1 (so sales w49/salesw48-1), again one column for each KPI

 

I need to structure that in a way that changing the last week number as per function below , I change all others parameters.

LW = WEEKNUM(TODAY())-3

 

Thanks in advance,

Andrea

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi great_AS 

     

    You can try measures like below

    LW Sales = CALCULATE(SUM('Table'[Sales]),'Table'[Week]=WEEKNUM(TODAY())-3)
    Sales Variation = DIVIDE([LW Sales],CALCULATE(SUM('Table'[Sales]),'Table'[Week]=WEEKNUM(TODAY())-4))-1

    For other KPIs, you can create similar measures. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi great_AS 

     

    You can try measures like below

    LW Sales = CALCULATE(SUM('Table'[Sales]),'Table'[Week]=WEEKNUM(TODAY())-3)
    Sales Variation = DIVIDE([LW Sales],CALCULATE(SUM('Table'[Sales]),'Table'[Week]=WEEKNUM(TODAY())-4))-1

    For other KPIs, you can create similar measures. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

  • PBIdashboards's avatar
    PBIdashboards
    Icon for Post Patron rankPost Patron
    4 months ago

    The accepted solution is correct. For the dynamic last week pattern, cleaner version using RANKX to avoid hardcoding the week number:

     
     

    Last Week Sales =
    VAR _lastWeek = MAXX(ALL('Calendar'[WeekNum]), 'Calendar'[WeekNum]) - 1
    RETURN
    CALCULATE(
    [Total Sales],
    ALL('Calendar'),
    'Calendar'[WeekNum] = _lastWeek
    )

    WoW % =
    DIVIDE([Total Sales] - [Last Week Sales], ABS([Last Week Sales]))

     

     

    This automatically picks up the second-most-recent week without needing to update the week number manually.

    For Country × KPI tables where the metric list keeps growing (adding profit margin, units, returns), each new KPI needs 2 more measures (absolute + WoW %). For ops teams doing this weekly reporting at scale, Flexa Tables on AppSource adds WoW variance as a built-in column  no additional DAX per KPI