compare same day data to a fix date range

[nardtmo]

Hi

Hoping you guys can help.

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

Thanks in advance!

					Output	Output
	week	day	date	# Customer	Baseperiod Ave. # customer	delta
Baseline period	27	Sunday	28-Jun	23
Baseline period	27	Monday	29-Jun	24
Baseline period	27	Tuesday	30-Jun	3
Baseline period	27	Wednesday	1-Jul	26
Baseline period	27	Thursday	2-Jul	27
Baseline period	27	Friday	3-Jul	28
Baseline period	27	Saturday	4-Jul	56
Baseline period	28	Sunday	5-Jul	30
Baseline period	28	Monday	6-Jul	31
Baseline period	28	Tuesday	7-Jul	4
Baseline period	28	Wednesday	8-Jul	33
Baseline period	28	Thursday	9-Jul	14
Baseline period	28	Friday	10-Jul	35
Baseline period	28	Saturday	11-Jul	9
Baseline period	29	Sunday	12-Jul	2
Baseline period	29	Monday	13-Jul	16
Baseline period	29	Tuesday	14-Jul	56
Baseline period	29	Wednesday	15-Jul	18
Baseline period	29	Thursday	16-Jul	19
Baseline period	29	Friday	17-Jul	20
Baseline period	29	Saturday	18-Jul	21
Future Data	30	Sunday	19-Jul	22	18.33333333	3.666667
Future Data	30	Monday	20-Jul	3	23.66666667	-20.6667
Future Data	30	Tuesday	21-Jul	24	21	3
Future Data	30	Wednesday	22-Jul	25	25.66666667	-0.66667
Future Data	30	Thursday	23-Jul	4	20	-16
Future Data	30	Friday	24-Jul	27	27.66666667	-0.66667
Future Data	30	Saturday	25-Jul	3	28.66666667	-25.6667
Future Data	31	Sunday	26-Jul	29	18.33333333	10.66667
Future Data	31	Monday	27-Jul	0	23.66666667	-23.6667
Future Data	31	Tuesday	28-Jul	31	21	10
Future Data	31	Wednesday	29-Jul	32	25.66666667	6.333333
Future Data	31	Thursday	30-Jul	18	20	-2
Future Data	31	Friday	31-Jul	5	27.66666667	-22.6667
Future Data	31	Saturday	1-Aug	29	28.66666667	0.333333
Future Data	32	Sunday	2-Aug	14	18.33333333	-4.33333
Future Data	32	Monday	3-Aug	2	23.66666667	-21.6667
Future Data	32	Tuesday	4-Aug	16	21	-5
Future Data	32	Wednesday	5-Aug	17	25.66666667	-8.66667
Future Data	32	Thursday	6-Aug	7	20	-13
Future Data	32	Friday	7-Aug	3	27.66666667	-24.6667
Future Data	32	Saturday	8-Aug	20	28.66666667	-8.66667
Future Data	33	Sunday	9-Aug	1	18.33333333	-17.3333
Future Data	33	Monday	10-Aug	22	23.66666667	-1.66667
Future Data	33	Tuesday	11-Aug	3	21	-18
Future Data	33	Wednesday	12-Aug	24	25.66666667	-1.66667
Future Data	33	Thursday	13-Aug	56	20	36
Future Data	33	Friday	14-Aug	8	27.66666667	-19.6667
Future Data	33	Saturday	15-Aug	27	28.66666667	-1.66667
Future Data	34	Sunday	16-Aug	28	18.33333333	9.666667
Future Data	34	Monday	17-Aug	0	23.66666667	-23.6667
Future Data	34	Tuesday	18-Aug	6	21	-15
Future Data	34	Wednesday	19-Aug	31	25.66666667	5.333333
Future Data	34	Thursday	20-Aug	2	20	-18
Future Data	34	Friday	21-Aug	33	27.66666667	5.333333
Future Data	34	Saturday	22-Aug	3	28.66666667	-25.6667
Future Data	35	Sunday	23-Aug	35	18.33333333	16.66667
Future Data	35	Monday	24-Aug	36	23.66666667	12.33333
Future Data	35	Tuesday	25-Aug	1	21	-20
Future Data	35	Wednesday	26-Aug	38	25.66666667	12.33333
Future Data	35	Thursday	27-Aug	80	20	60
Future Data	35	Friday	28-Aug	10	27.66666667	-17.6667

[Greg_Deckler]

@nardtmo - I *think* you want something like:

Measure =
  VAR __Weekday = MAX('Table'[day])
  VAR __Baseline = AVERAGEX(FILTER(ALL('Table'),([week] = "Baseline period27" || [week] = "Baseline period28" || [week] = "Baseline period29") && [day]=__Weekday),[#])
  VAR __Current = MAX('Table'[#])
RETURN
  __Current - __Baseline

[nardtmo]

Hi Greg,

Thanks for the reply. if you have time pls take a look at the pbix. I added date table and use a measure to sum all customers. the plan is, i should be able to compare daily #of customer from baseline period as well as  compare daily# of customer from specific stores (same calculation) 

I'm getting error

Delta = VAR __Weekday = MAX('Date Table'[day name])

VAR __Baseline = AVERAGEX(FILTER(ALL('Date Table'[week]),([week] = 27 || [week] = 28 || [week] = 29) && [day]=__Weekday),[sum of customers])

VAR __Current = MAX([sum of customers])

RETURN

__Current - __Baseline

error ="Column 'day' cannot be bound or not be use in this expression"

https://drive.google.com/file/d/1DbPDD5-2ZcKrP2U36Cs5r6vY0smQAniS/view?usp=sharing

thanks again.

[vivran22]

Hello @nardtmo ,

Unable to access the file as the access is denied.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[nardtmo]

Helloo

uploaded it in dropbox.

https://www.dropbox.com/s/kcoau2k2vd7xvsk/delta.pbix?dl=0

[vivran22]

@nardtmo 

I can access the file. Can you explain how did you get 18.33 as Baseperiod Ave. # customer?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[nardtmo]

18.33 it's the average of all Sundays of WK 27,28, & WK 29

[vivran22 · Accepted solution]

@nardtmo 

You may use the following measures:

Average Last 3 weeks = 
VAR _CurrentDate =
    MAX ( 'Table'[date] )
VAR _Weekday =
    WEEKDAY ( _CurrentDate, 2 )
VAR _MinDate =
    CALCULATE ( MIN ( 'Table'[date] ), ALL ( 'Table'[date] ) )
VAR _Average =
    IF (
        _CurrentDate >= _MinDate + 21,
        CALCULATE (
            AVERAGE ( 'Table'[# Customer] ),
            DATESINPERIOD ( 'Table'[date], _CurrentDate - 1, -21, DAY ),
            WEEKDAY ( 'Table'[date], 2 ) = _Weekday
        )
    )
RETURN
    _Average




Difference = 
VAR _CurrentDate =
    MAX ( 'Table'[date] )
VAR _MinDate =
    CALCULATE ( MIN ( 'Table'[date] ), ALL ( 'Table'[date] ) )
VAR _Difference =
    IF (
        _CurrentDate >= _MinDate + 21,
        MAX ( 'Table'[# Customer] ) - [Average Last 3 weeks]
    )
RETURN
    _Difference

Output

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[nardtmo]

Thanks so much!!