Calculate percentage change from previous day, week, 2 weeks and 1 month

[lc1]

Hello, please see data below, I need to create a calculated formula that shows a percentage rate from previous day, week and month...thank you!

			% Change
		Feb	1 Day	7 Days	Monthly
Wed	1-Jan	63,795
Thu	2-Jan	146,777
Fri	3-Jan	160,004
Sat	4-Jan	99,753
Sun	5-Jan	74,797
Mon	6-Jan	164,577
Tue	7-Jan	167,543
Wed	8-Jan	166,574
Thu	9-Jan	165,948
Fri	10-Jan	163,512
Sat	11-Jan	96,151
Sun	12-Jan	72,176
Mon	13-Jan	168,781
Tue	14-Jan	169,576
Wed	15-Jan	170,661
Thu	16-Jan	169,131
Fri	17-Jan	158,694
Sat	18-Jan	99,953
Sun	19-Jan	76,833
Mon	20-Jan	123,146
Tue	21-Jan	164,266
Wed	22-Jan	147,170
Thu	23-Jan	154,563
Fri	24-Jan	166,008
Sat	25-Jan	98,568
Sun	26-Jan	76,067
Mon	27-Jan	166,081
Tue	28-Jan	169,168
Wed	29-Jan	166,539
Thu	30-Jan	165,867
Fri	31-Jan	171,535
Sat	1-Feb	90,399
Sun	2-Feb	72,113
Mon	3-Feb	173,706
Tue	4-Feb	175,061
Wed	5-Feb	171,828
Thu	6-Feb	169,780
Fri	7-Feb	145,596
Sat	8-Feb	98,272
Sun	9-Feb	74,797
Mon	10-Feb	170,918
Tue	11-Feb	171,546
Wed	12-Feb	170,329
Thu	13-Feb	170,216
Fri	14-Feb	162,203
Sat	15-Feb	88,435
Sun	16-Feb	74,457
Mon	17-Feb	135,897
Tue	18-Feb	171,421
Wed	19-Feb	168,039
Thu	20-Feb	167,489
Fri	21-Feb	165,576
Sat	22-Feb	93,598
Sun	23-Feb	78,275
Mon	24-Feb	171,756
Tue	25-Feb	168,971
Wed	26-Feb	163,917
Thu	27-Feb	163,774
Fri	28-Feb	164,633
Sat	29-Feb	95,405
Sun	1-Mar	73,341
Mon	2-Mar	168,439
Tue	3-Mar	176,669
Wed	4-Mar	171,526
Thu	5-Mar	167,148
Fri	6-Mar	158,497
Sat	7-Mar	95,466
Sun	8-Mar	70,741
Mon	9-Mar	161,254
Tue	10-Mar	163,830
Wed	11-Mar	158,580
Thu	12-Mar	154,272
Fri	13-Mar	150,020
Sat	14-Mar	84,155
Sun	15-Mar	61,535
Mon	16-Mar	120,287
Tue	17-Mar	108,808
Wed	18-Mar	97,717
Thu	19-Mar	89,415
Fri	20-Mar	80,129

[Ashish_Mathur]

Hi,

Why is there a Feb heading there?  Show the exact result that you are expecting

[lc1]

Hi, thank you all for your replies, here is what I did (formula and results)

My concern is on the weekly and monthly that is not comparing apples to apples.

1- How can I exclude saturdays and sundays from the weekly and monthly equation?

2- Also, do you have any visuals you recommend using to better reflect the trend or is line and bars the best one?

Formulas used:

Previous Day = CALCULATE([BusTotalCount],PREVIOUSDAY('Date'[Date]))

Previous Week = CALCULATE(RidershipBus[BusTotalCount],DATEADD('Date'[Date],-7,DAY))

Previous Month = CALCULATE(RidershipBus[BusTotalCount],DATEADD('Date'[Date],-1,MONTH))

1D Chg = ([BusTotalCount]-[Previous Day])/[Previous Day]

1D Chg = ([BusTotalCount]-[Previous Day])/[Previous Week]

1D Chg = ([BusTotalCount]-[Previous Day])/[Previous Month]

[lc1]

I'm having a hard time filtering out Saturday and Sunday from this formula:

Previous Day = CALCULATE([BusTotalCount],PREVIOUSDAY('Date'[Date])) 

[lc1]

After reviewing the data again what I need is the value for the same weekday of the previous month.

Eg. Compare this Friday's sales to same day (Friday) 4 weeks ago (Previous month)

[littlemojopuppy]

The formulas you posted for Previous Day/Week/Month should give you the results you want

[lc1]

I need to be able to compare results of same day of the week.

Monday to Monday, Tuesday to Tuesday, Sat to Sat, etc

[Ashish_Mathur]

Hi,

Try these measures

Total sales = SUM(Data[Sale])

Tota sales a week ago = CALCULATE([Total sales],DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-7,MIN(Calendar[Date])-7))

[lc1]

Hi Ashish,

The week measure is working fine (it is comparing same day), I need help with the previous month.

How can I show the sales on the same day (Mon, Tues, Wed....) 4 weeks ago?

[Ashish_Mathur]

I cannot understand your question.  If today is Monday, March 23, 2020, with which date of the previous month do you want to compare the sales?

[lc1]

Monday 2/24/20

(4 weeks ago, on the same day of the week)

[littlemojopuppy]

Hello!

To exclude Saturday and Sunday, you want to include the following where it refers to dates. I'm writing this in Notepad so it may not be syntactically perfect...

FILTER(
 Date,
 WEEKDAY(Date[Date]) IN {1, 2, 3, 4, 5}
)

But you seem to have data on Saturdays and Sundays, so why would you want to exclude them?

The column chart does a good presentation job... that's what I'd choose.

[amitchandak]

In case you need to compare with 1 day behind, 7 day behind or 1 month behind measure the do like

day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,day))
week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,day))
month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,month))

The create change %

In case you need mom, QOQ etc you can use following as 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)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))


YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Greg_Deckler]

If you have trouble with time intelligence functions, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[littlemojopuppy]

This should be fairly simple using some time intelligence functions...