topic date comparison in DAX Commands and Tips

date comparison

nardtmo — Mon, 24 Aug 2020 22:35:52 GMT

Hello

Stuck with a problem. Appreciate anyone who can help!!!

Want to create thist table. I have the date table and # of customers. Need help to get ave. # customer and difference.

Re: date comparison

Greg_Deckler — Mon, 24 Aug 2020 23:01:49 GMT

nardtmo - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __Previous

If you need more specificity, please post data as text in table.

Re: date comparison

amitchandak — Tue, 25 Aug 2020 02:26:31 GMT

nardtmo , you refer to my week by week blog. How to use week Rank to this week vs last week

example

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense) 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)) Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Blogs : 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-Last-Week/ba-p/1051123

How to get a week calendar:Any Weekday Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

Re: date comparison

nardtmo — Fri, 28 Aug 2020 10:26:37 GMT

Greg,

Basically i want to use Week 28-30 as my comparison week/weekday for succeeding days. Say today is 8/28 friday, it will take friday data of week 28-30, average it and compare is to 8/28 friday data.

I was able to do it with a slicer with selected date or value (see measure below) but what i wanted to do is plot the % change every day starting from WK31 (comparing the day/s to average of the same day of week 28-week 30)

Hope this clarify things. thanks in ADVANCE!!!

Customer %Change =

VAR WK27 = CALCULATE([LTE DL Volume],

Filter(all('Date Table'),

'Date Table'[week]=27 &&

'Date Table'[week day]= SELECTEDVALUE('Date Table'[week day])

))

VAR WK28 = CALCULATE([LTE DL Volume],

Filter(all('Date Table'),

'Date Table'[week]=28 &&

'Date Table'[week day]= selectedvalue('Date Table'[week day])

))

VAR WK29 = CALCULATE([LTE DL Volume],

Filter(all('Date Table'),

'Date Table'[week]=29 &&

'Date Table'[week day]= selectedvalue('Date Table'[week day])

))

VAR #ave Customer = CALCULATE((wk28+wk29+wk30)/3)

return

Divide ([Sum of customer]-#ave Customer,#ave Customer,0)

	week	day	date	# Customer
Baseline period	27	Monday	29-Jun	10
Baseline period	27	Tuesday	30-Jun	10
Baseline period	27	Wednesday	1-Jul	10
Baseline period	27	Thursday	2-Jul	10
Baseline period	27	Friday	3-Jul	10
Baseline period	27	Saturday	4-Jul	10
Baseline period	28	Sunday	5-Jul	10
Baseline period	28	Monday	6-Jul	10
Baseline period	28	Tuesday	7-Jul	10
Baseline period	28	Wednesday	8-Jul	10
Baseline period	28	Thursday	9-Jul	10
Baseline period	28	Friday	10-Jul	10
Baseline period	28	Saturday	11-Jul	10
Baseline period	29	Sunday	12-Jul	10
Baseline period	29	Monday	13-Jul	10
Baseline period	29	Tuesday	14-Jul	10
Baseline period	29	Wednesday	15-Jul	10
Baseline period	29	Thursday	16-Jul	10
Baseline period	29	Friday	17-Jul	10
Baseline period	29	Saturday	18-Jul	10
Baseline period	30	Sunday	19-Jul	10
Baseline period	30	Monday	20-Jul	10
Baseline period	30	Tuesday	21-Jul	10
Baseline period	30	Wednesday	22-Jul	10
Baseline period	30	Thursday	23-Jul	10
Baseline period	30	Friday	24-Jul	10
Baseline period	30	Saturday	25-Jul	10
Baseline period	31	Sunday	26-Jul	10
Baseline period	31	Monday	27-Jul	10
Baseline period	31	Tuesday	28-Jul	10
Baseline period	31	Wednesday	29-Jul	10
Baseline period	31	Thursday	30-Jul	11
Baseline period	31	Friday	31-Jul	12
Baseline period	31	Saturday	1-Aug	13
Future Data	32	Sunday	2-Aug	14
Future Data	32	Monday	3-Aug	15
Future Data	32	Tuesday	4-Aug	16
Future Data	32	Wednesday	5-Aug	17
Future Data	32	Thursday	6-Aug	18
Future Data	32	Friday	7-Aug	19
Future Data	32	Saturday	8-Aug	20
Future Data	33	Sunday	9-Aug	21
Future Data	33	Monday	10-Aug	22
Future Data	33	Tuesday	11-Aug	23
Future Data	33	Wednesday	12-Aug	24
Future Data	33	Thursday	13-Aug	25
Future Data	33	Friday	14-Aug	26
Future Data	33	Saturday	15-Aug	27
Future Data	34	Sunday	16-Aug	28
Future Data	34	Monday	17-Aug	29
Future Data	34	Tuesday	18-Aug	30
Future Data	34	Wednesday	19-Aug	31
Future Data	34	Thursday	20-Aug	32
Future Data	34	Friday	21-Aug	33
Future Data	34	Saturday	22-Aug	34
Future Data	35	Sunday	23-Aug	35
Future Data	35	Monday	24-Aug	36
Future Data	35	Tuesday	25-Aug	37
Future Data	35	Wednesday	26-Aug	38
Future Data	35	Thursday	27-Aug	39
Future Data	35	Friday	28-Aug	40