Same day last week

[Anonymous]

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	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

But 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:

Location	Department	Current Date	M.Sameday_LW Sales
101	12	24	125

Expected values:

Location	Department	Current Date	M.Sameday_LW Sales
101	12	24	17

Thanks in Advance!

[amitchandak · Accepted solution]

@Anonymous ,

Basically 7 days behind measure will keep on running past and give you data unless you filter a date . when you select

 a date in the calendar then it will stop

M.Sameday_LW Sales = CALCULATE(SUM(vTransaction[EX_GST_AMT]),
DATEADD('Dim Date'[CALENDAR_DATE_FMTD],-7,DAY))

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))

but this will also depend on a max date to take max date from the table if needed

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))

[Anonymous]

HI @Anonymous 

Probably, you are using the DAX mentioned by you as a measure.

Create a column using the same DAX and your issue will be resolved.