KPI for week versus previous/prior week and month versus last year month

[user_unknown39]

Hi All,

I am super new to Power BI and trying to create a KPI for-

1. week versus previous week with percent change ( March 13, 2020, vs. March March 6, 2020)

2. Month versus last year's month with percent change. (For e.g: March 2021 vs.  March 2020)

Similar to this

My current data look like this and goes back two years. I have weekly data for each of the locations.

Store Name    Location           Rev$     Week #

Store A           London             20         2021-02-27
Store B           Amsterdam       10         2021-02-27
Store C           New York          15         2021-02-27
Store A           London             30         2021-03-06
Store B           Amsterdam       40         2021-03-06
Store C           New York          50         2021-03-06
Store A           London             20         2021-03-13
Store B           Amsterdam       40         2021-03-13
Store C           New York         50          2021-03-13

What would be the best way to do this?

Thank you.

[v-yangliu-msft · Accepted solution]

Hi  @user_unknown39 ,

According to your description, I create some data:

Here are the steps you can follow：

1. Create calculated column.

Year = YEAR('Table'[Week #])

Month = MONTH('Table'[Week #])

Weeknum = WEEKNUM('Table'[Week #])

2. Create measure.

week versus previous week with percent change:

current_week =
var _select=SELECTEDVALUE('Table'[Week #])
return
CALCULATE(SUM('Table'[Rev$]),FILTER(ALL('Table'),'Table'[Year]=YEAR(_select)&&'Table'[Weeknum]=WEEKNUM(_select)))

last_week =
var _select=SELECTEDVALUE('Table'[Week #])
return
CALCULATE(SUM('Table'[Rev$]),FILTER(ALL('Table'),'Table'[Year]=YEAR(_select)&&'Table'[Weeknum]=WEEKNUM(_select)-1))

Month versus last year's month with percent change:

current_month =
var _select=SELECTEDVALUE('Table'[Week #])
return
CALCULATE(SUM('Table'[Rev$]),FILTER(ALL('Table'),'Table'[Year]=YEAR(_select)&&'Table'[Month]=MONTH(_select)))

lastyear_month =
var _select=SELECTEDVALUE('Table'[Week #])
return
CALCULATE(SUM('Table'[Rev$]),FILTER(ALL('Table'),'Table'[Year]=YEAR(_select)-1&&'Table'[Month]=MONTH(_select)))

3. Create a slicer named 'Table'[Week #], and put the field in the KPI as a picture

Related to month:

Related to week:

4. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[JamesLow]

What is the name of the visual used please?

[user_unknown39]

Hi @v-yangliu-msft ,

When I select multiple weeks in the sliders, this doesn't work correctly.

So for example if I select 2021-03-06 and 2021-03-13, it doesn't show the sum of both weeks.

How can I resolve this?

Thank-you

[user_unknown39]

Thank you @v-yangliu-msft. This is exactly what I was looking for! Appreciate it.

How will I do YTD in the same way? my fiscal year ends February.

[Ashish_Mathur]

Hi,

The geneal pattern is

=calculate(sum(data[Rev$]),datesytd(calendar[date],"28/2"))

Hope this helps.

[amitchandak]

@user_unknown39 , You can use date table time intelligence

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Week you can do with week Rank

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

For this, you need these columns in Date table

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

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[user_unknown39]

Hi Amit, 

Thank you for your quick response.

How will I compare February 2021 sales with Feb 2020? We usually get our data a little late so MTD won't work.

It would be great if you could use my columns in the formula?

It's a little confusing as I am super new to this.

Thanks!