Current week vs last week sum

[bo_afk]

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

[arpitsa]

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

[v-shex-msft]

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]

Thanks for your response @v-shex-msft.

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?

[v-shex-msft · Accepted solution]

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