cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

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

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.

1 ACCEPTED SOLUTION
Community Support

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：

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.

7 REPLIES 7
Community Support

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：

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.

Frequent Visitor

What is the name of the visual used please?

Helper I

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

Helper I

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.

Super User

Hi,

The geneal pattern is

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@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

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.

Helper I

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!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors