How to show percentage increase metric (week on week or month on month) depending on slicer?

[adilk]

Hi,

We have a weekly dashboard that can also double up as a monthly one.

I already have a 'Card' to show the metric for the current week. I would like Power BI to identify the date selection in the slicer and then show the % increase comparing current week to previous week and show % increase.

Also, if the selection is an entire month, then show the Card metric (% increase ) compared to previous month, not previous same number of days. How can I accomplish this?

Very new to Power BI . Thanks.

Thanks.

[v-shex-msft · Accepted solution]

Hi @adilk,

Perhpas you can take a look at below formulas:

Add a table to store the calculate date range.

Table formula:

DateUnit = UNION(ROW("Unit","Day"),ROW("Unit","Week"),ROW("Unit","Month"),ROW("Unit","Year"))

Write the measures to calculate the specify data range.

Measures:

SelectUnit = IF(HASONEVALUE(DateUnit[Unit]),VALUES(DateUnit[Unit]),BLANK()) // get the select item from slicer

CurrentDate = MAX(Test[Date]) // current date from source table

Dayly Increase = 
var currAmount= SUMX(FILTER(ALL(Test),Test[Date]=[CurrentDate]),[Amount])
var previousAmount=SUMX(FILTER(ALL(Test),Test[Date]=[CurrentDate]-1),[Amount])
return
(previousAmount-currAmount)/previousAmount

Weekly Increase = 
var currAmount= SUMX(FILTER(ALL(Test),Test[Date].[Year]=YEAR([CurrentDate])&&WEEKNUM(Test[Date])=WEEKNUM([CurrentDate])),[Amount])
var previousAmount=SUMX(FILTER(ALL(Test),Test[Date].[Year]=YEAR([CurrentDate])&&WEEKNUM(Test[Date])=WEEKNUM([CurrentDate])-1),[Amount])
return
(previousAmount-currAmount)/previousAmount

Monthly Increase = 
var currAmount= SUMX(FILTER(ALL(Test),Test[Date].[Year]=YEAR([CurrentDate])&&MONTH(Test[Date])=MONTH([CurrentDate])),[Amount])
var previousAmount=SUMX(FILTER(ALL(Test),Test[Date].[Year]=YEAR([CurrentDate])&&MONTH(Test[Date])=MONTH([CurrentDate])-1),[Amount])
return
(previousAmount-currAmount)/previousAmount

Yearly Increase = 
var currAmount= SUMX(FILTER(ALL(Test),Test[Date].[Year]=YEAR([CurrentDate])),[Amount])
var previousAmount=SUMX(FILTER(ALL(Test),Test[Date].[Year]=YEAR([CurrentDate])-1),[Amount])
return
(previousAmount-currAmount)/previousAmount

DynamicIncrease = SWITCH([SelectUnit],"Day",[Dayly Increase],"Week",[Weekly Increase],"Month",[Monthly Increase],"Year",[Yearly Increase])

BTW, you should add some condition to deal with the year/month/week convert issue.

Regards,

Xiaoxin Sheng

[MattAllington]

If you can wait 12 hours, I have a blog post going live that will help you with this. 

Edit:  Here is the blog article http://exceleratorbi.com.au/dax-time-intelligence-beginners/

[adilk]

cool, thx. will wait for the blog post.