Weekly sales variance % format Measure

[Lynzmaz]

Hi there,

I have spent many many days trying to get a measure to work to compare this week vs last week net sales in % format per store. Not sure if my calendar is set up wrong or where is the problem. Please help!!!! 

Thanks so much!

[v-henryk-mstf]

Hi @Lynzmaz ,

Sorry, it seems that the link you shared cannot be opened because there are no corresponding permissions. Could you share it again by another way.

Looking forward to your reply.

Best Regards,
Henry

[v-henryk-mstf]

Hi @Lynzmaz ,

According to your description, a separate calendar table needs to be created. Then create another column to find the number of days of the week corresponding to the date. The measure created to find the difference between the two weeks corresponds to the following reference:

Num = WEEKNUM('calendar'[Date],2)

M_ = 
VAR a =
    WEEKNUM ( TODAY (), 2 )
RETURN
    CALCULATE ( SUM ( 'Table'[Value] ), a = 'calendar'[Num] )
        - CALCULATE ( SUM ( 'Table'[Value] ), a = 'calendar'[Num] - 1 )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Do you have a Calendar Table with a week number column?  If not, please create one.

[amitchandak]

@Lynzmaz , Create a date table with following columns

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

then create  measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Power BI — Week on Week and WTD
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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Lynzmaz]

Hi Amit,

I have followed the steps but it is still not pulling the information. Where have I gone wrong?

Yes, I do have a calendar with week numbers already. I don't think the week rank is working.

Could I send the .pbix file for you to review?

Thanks

[v-henryk-mstf]

Hi @Lynzmaz ,

You can share the link to your pbix file and I will answer it for you as soon as possible.

Best Regards,
Henry

[Lynzmaz]

Hi Henry,

Hopefully this link works.

Thankshttps://app.powerbi.com/links/7eAX4BEMFg?ctid=9b1e8475-9b62-4119-97e4-d9746010be83&pbi_source=linkSh...