Forum Discussion

bo_afk's avatar
bo_afk
Post Patron
7 years ago
Solved

Current week vs last week sum

Hi,

 

I have a dataset containing week numbers and sales figures associated to each week, along with other columns such as market, product etc.

 

How would do I create a measure to compare the sales figures between one week and the previous week? Assuming that I have a table displaying current weeks' sales.

 

Thanks

 

afk

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi bo_afk ,

    You can add allselected function to get current last week number and use it to find out correspond amount:

    CW Sales =
VAR CW =
    CALCULATE ( MAX ( Table[WeekNum] ), ALLSELECTED ( Table ) )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER ( ALLSELECTED ( Table ), Table[WeekNum] = CW )
    )

    Regards,

    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi bo_afk ,

    You can use following measure formula if it suitable for your requirement:

    Diff =
VAR currWeek =
    MAX ( Table[WeekNum] )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER ( ALLSELECTED ( Table ), [WeekNum] = currWeek ),
        VALUES ( Table[Product] ),
        VALUES ( Table[Market] )
    )
        - CALCULATE (
            SUM ( Table[Sales] ),
            FILTER ( ALLSELECTED ( Table ), [WeekNum] = currWeek - 1 ),
            VALUES ( Table[Product] ),
            VALUES ( Table[Market] )
        )

    If above not help, please share some sample data for test.

    Regards,

    Xiaoxin Sheng

    • bo_afk's avatar
      bo_afk
      Post Patron
      7 years ago

      Thanks for your response Anonymous.

       

      I've however encountered an issue in cases where a particular product does not have any sales for the current week. I currently have a measure that just to looks at the CW sales figures as per below.

       

      CW Sales = 
      var CW = max(Table[WeekNum])
      RETURN 
      CALCULATE (sum (Table[Sales]), Table[WeekNum] = CW)

      But it's returning results even for products that aren't sold in the CW. Looks like it is taking the max week for THAT product and then returning the sales associated to that week as opposed to the latest week overall in the data.

       

      How can I fix this?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi bo_afk ,

        You can add allselected function to get current last week number and use it to find out correspond amount:

        CW Sales =
VAR CW =
    CALCULATE ( MAX ( Table[WeekNum] ), ALLSELECTED ( Table ) )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER ( ALLSELECTED ( Table ), Table[WeekNum] = CW )
    )

        Regards,

        Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    i want to calculate last week vs current week comparison bases on total backoffice cases [ for eg previous week it is 100 and this week till date 110 so variance is +10 ] and i need to show this value in power Bi Card visual

     

    Can someone please assist me in - i have tried referrring various sites but i am not getting appropriate codes