Forum Discussion

phanimusunuri9's avatar
phanimusunuri9
Frequent Visitor
8 years ago
Solved

Week over Week Comparison

Hi All,

 

I am trying to compare "Monday Last week's Sales" with "Monday this week's Sales". To be precise, if today is 'Monday' i want to compare it with last 'Monday', if 'Tuesday then last week 'Tuesday'. so on..

 

Sales last Monday = 2,450

Sales this Monday = 2,360

 

Difrerence = 90 (3.6%)

 

Is there a way to do this as a 'Measure', i want this in KPI visual.

Need Help
Tips and Tricks
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    phanimusunuri9

     

    You should have Year, Week Number and Weekday columns in your table. Or you can generate with calculated column. 

     

     

    WeekNumber = WEEKNUM(Table[Date],2)
    WeekDay = WEEKDAY(Table[Date],2)

    Then you can create a measure like: 

     

     

     

    Difference =
SUM ( Table[Sales] )
    - CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            ALL ( Table ),
            Table[Year] = MAX ( Table[Year] )
                && Table[WeekNumber]
                    = MAX ( Table[WeekNumber] ) - 1
                && Table[WeekDay] = MAX ( Table[WeekDay] )
        )
    )

    Regards,

15 Replies

  • phanimusunuri9's avatar
    phanimusunuri9
    Frequent Visitor
    8 years ago

    Hi All,

     

    I am trying to compare "Monday Last week's Sales" with "Monday this week's Sales". To be precise, if today is 'Monday' i want to compare it with last 'Monday', if 'Tuesday then last week 'Tuesday'. so on..

     

    Sales last Monday = 2,450

    Sales this Monday = 2,360

     

    Difrerence = 90 (3.6%)

     

    Is there a way to do this as a 'Measure', i want this in KPI visual.

     

     

     

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      8 years ago

      phanimusunuri9

       

      You should have Year, Week Number and Weekday columns in your table. Or you can generate with calculated column. 

       

       

      WeekNumber = WEEKNUM(Table[Date],2)
      WeekDay = WEEKDAY(Table[Date],2)

      Then you can create a measure like: 

       

       

       

      Difference =
SUM ( Table[Sales] )
    - CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            ALL ( Table ),
            Table[Year] = MAX ( Table[Year] )
                && Table[WeekNumber]
                    = MAX ( Table[WeekNumber] ) - 1
                && Table[WeekDay] = MAX ( Table[WeekDay] )
        )
    )

      Regards,

      • jcabeza's avatar
        jcabeza
        Frequent Visitor
        5 years ago

        I have problems when i have change of the year

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Ashish,

       

      Can you please assist me in calculating last week vs current week comparison in power bi +-

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

        Share some data and show the expected result.  More importantly, in the Calendar table, please show a column with the week numbers.

  • mase_53's avatar
    mase_53
    New Member
    4 years ago

    Hi Ashish_Mathur phanimusunuri9 Anonymous jcabeza is there a way to use this solution - when my Year, Week Number and Weekday columns live in another table (dim_dates) but have an active relationship with the date in the table containing sales? Would appreciate your help!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      Share some data, describe the question and show the expected result.