Forum Discussion

Miguelcrz's avatar
Miguelcrz
Helper I
6 years ago

WEEK VARIATION

Hello,

 

I need to have the variation % of my sales comparing it vs the last week like this:

 

 

 

My power BI currently looks like this:

 

And my table of time looks like this (Semanacal = Number of the week):

 

 

I've also looked at the forum the questions of people that had the same issue but isn't working for me.

 

Thanks!

 

 

Need Help

10 Replies

  • Anand24's avatar
    Anand24
    Super User
    6 years ago

    Hi Miguelcrz ,

     

    Can you create a new column with below DAX:

    Last Calendar Week Sales = CALCULATE( [Total Sales], 'Calendar'[Week Number] = WEEKNUM(TODAY(),1) -1 )

     

    Use this to create another calculated column for % variation with below DAX:

    Percent_Variation = (([Total Sales]-[Last Calendar Week Sales])/[Last Calendar Week Sales])*100

     

    Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

    • Miguelcrz's avatar
      Miguelcrz
      Helper I
      6 years ago

      Hi! is not working. I only have the column week and the column sales. I have to calculate or do a new column to calculate the sales from last week but i dont know how. Then i need to calculate the variation between the sales of this week vs last week.

      Thanks!

      • FrankAT's avatar
        FrankAT
        Community Champion
        6 years ago

        Hi @Miguelcrz

        here another solution to your question. Use the three measures below.

        15-08-_2020_01-53-27.png

        Regards FrankAT

    • Miguelcrz's avatar
      Miguelcrz
      Helper I
      6 years ago

      Hi @Anand24 @amitchandak @v-shex-msft @FrankAT ! is not working. I only have the column week and the column sales. I have to calculate or do a new column to calculate the sales from last week but i dont know how. Then i need to calculate the variation between the sales of this week vs last week.

      Thanks!

      • FrankAT's avatar
        FrankAT
        Community Champion
        6 years ago

        Hi Miguelcrz 

        here is another solution to your question. Use the three measures below.

         

         

        Regards FrankAT

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Miguelcrz,

    You can try to use the following measure formula if it meets your requirement.

    Measure =
VAR currWeek =
    MAX ( table[Week] )
VAR prevSales =
    CALCULATE (
        SUM ( table[Sales] ),
        FILTER ( ALLSELECTED ( table ), [Week] = currWeek - 1 )
    )
RETURN
    DIVIDE ( SUM ( table[Sales] ) - prevSales, prevSales, -1 )

    If above not help, please provide some dummy data to help us clarify your scenario and do test coding formula on it.
    Regards,

    Xiaoxin Sheng

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Miguelcrz , first of all have a separate week table. Should include the year in that.  The best way is to use week year rank , Please follow my blog for that.

    With week no. The date can Week -year/week table

    This Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week]=max('Date'[Week])))
    Last Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week]=max('Date'[Week])-1))

     

    With Rank

    This Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    refer these to see how rank works

    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123