Same day last week

Question

Hi Folks,I am using the below dax for calculating Sales for Same day last week:M.Sameday_LW Sales = CALCULATE(SUM(vTransaction[EX_GST_AMT]),DATEADD('Dim Date'[CALENDAR_DATE_FMTD],-7,DAY))Which is working fine is used in the below format(i.e. going date by date):Location&nbsp; &nbsp; &nbsp; &nbsp;Department&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR_DATE_FMTD&nbsp; &nbsp; Current Date&nbsp; &nbsp; &nbsp; &nbsp;M.Sameday_LW Sales101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30/09/2019 0:00&nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; 1/10/2019 0:00&nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; 2/10/2019 0:00&nbsp; &nbsp; 13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; 3/10/2019 0:00&nbsp; &nbsp;&nbsp; &nbsp; 14&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; 4/10/2019 0:00&nbsp; &nbsp; 15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; 5/10/2019 0:00&nbsp; &nbsp; 16&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp;101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; 6/10/2019 0:00&nbsp; &nbsp; 17&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; 7/10/2019 0:00&nbsp; &nbsp; 18&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; 8/10/2019 0:00&nbsp; &nbsp; 19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; 9/10/2019 0:00&nbsp; &nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;13101&nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp;10/10/2019 0:00&nbsp; &nbsp; 21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;14101&nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; 11/10/2019 0:00&nbsp; &nbsp; 22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15101&nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; 12/10/2019 0:00&nbsp; &nbsp; 23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;16101&nbsp; &nbsp; &nbsp; 1213/10/2019 0:00&nbsp; &nbsp; 24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;17Total&nbsp;&nbsp;&nbsp; &nbsp;244&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;125But Fails when used in the below format(i.e. if we eliminate the date factor) its just shows the sum of the entire date selection instead of showing same day last week value which here should be $17:&nbsp;Location&nbsp; &nbsp; &nbsp; Department&nbsp; &nbsp; &nbsp;Current Date&nbsp; &nbsp; &nbsp; &nbsp; M.Sameday_LW Sales101&nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp;24&nbsp; &nbsp; &nbsp; &nbsp;125Expected values:&nbsp;Location&nbsp; &nbsp; &nbsp; Department&nbsp; &nbsp; &nbsp; Current Date&nbsp; &nbsp; M.Sameday_LW Sales101&nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; 24&nbsp; &nbsp; &nbsp; 17&nbsp;Thanks in Advance!

amitchandak · Accepted Answer

Anonymous ,
Basically 7 days behind measure will keep on running past and give you data unless you filter a date . when you select
&nbsp;a date in the calendar then it will stop
&nbsp;
M.Sameday_LW Sales = CALCULATE(SUM(vTransaction[EX_GST_AMT]),DATEADD('Dim Date'[CALENDAR_DATE_FMTD],-7,DAY))
&nbsp;
This set should work better
This Day = CALCULATE(sum(vTransaction[EX_GST_AMT]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))M.Sameday_LW Sales = CALCULATE(sum(vTransaction[EX_GST_AMT]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-7))
&nbsp;
but this will also depend on a max date to take max date from the table if needed
&nbsp;
This Day = CALCULATE(sum(vTransaction[EX_GST_AMT]), FILTER(ALL('Date'),'Date'[Date]=max('vTransaction'[Date])))M.Sameday_LW Sales = CALCULATE(sum(vTransaction[EX_GST_AMT]), FILTER(ALL('Date'),'Date'[Date]=max('vTransaction'[Date])-7))
&nbsp;

Forum Discussion

Same day last week

2 Replies

Related content

Week over week measure

Week over Week Diff

Week over Week Change

ISO week

Utiisation per week

Location	Department	CALENDAR_DATE_FMTD	Current Date	M.Sameday_LW Sales
101	12	30/09/2019 0:00	10	1
101	12	1/10/2019 0:00	12	2
101	12	2/10/2019 0:00	13	3
101	12	3/10/2019 0:00	14	4
101	12	4/10/2019 0:00	15	5
101	12	5/10/2019 0:00	16	6
101	12	6/10/2019 0:00	17	7
101	12	7/10/2019 0:00	18	10
101	12	8/10/2019 0:00	19	12
101	12	9/10/2019 0:00	20	13
101	12	10/10/2019 0:00	21	14
101	12	11/10/2019 0:00	22	15
101	12	12/10/2019 0:00	23	16
101	12	13/10/2019 0:00	24	17
Total			244	125