Last Week sales not filtering well

[Anonymous]

Hi All,

I have calculated the previous week sales using following measure:

Sales WOW =

var C_week = SELECTEDVALUE('Date'[Week])

var C_Yesr = SELECTEDVALUE('Date'[Year])

var Max_Week = CALCULATE(MAX('Date'[Week]),ALL('Date'))

RETURN

SUMX(

FILTER(ALL('Date'),

IF(C_week = 1,

'Date'[Week] = Max_Week && 'Date'[Year] = C_Yesr - 1,

'Date'[Week] = C_week - 1 && 'Date'[Year] = C_Yesr)),

[Sales])

The result showing correct, but when I select date from another visual, it is not showing the previous week highlighted sales correct.

As below:

Please help me.

Thanks

[Anonymous · Accepted solution]

Hi All,

I have resolved the issue.

I have make some change in measure to find the Last Week sale:

Sales WOW =
var C_week = SELECTEDVALUE('Date'[Week])
var C_Year = SELECTEDVALUE('Date'[Year])
var Max_Week = CALCULATE(MAX('Date'[Week]),ALL('Date'))

RETURN
CALCULATE([Sales], ALL('Date'),

FILTER(ALL('Date'),
IF(C_week = 1,
'Date'[Week] = Max_Week && 'Date'[Year] = C_Year - 1,
'Date'[Week] = C_week - 1 && 'Date'[Year] = C_Year)
),VALUES('Date'[week Day])
)

Now the last week sales shows correct and also successfully filters from the other visuals.(Showing correct data hor highlighted sales)

Thanks.

[amitchandak]

@Anonymous , with help from these columns

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

try measures like
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))

both visual should use date from date table

[Anonymous]

Hi @amitchandak,

It isnot working Amit, now both ThisWeek and LastWeek measures not showing the Highlighted sales, instead of that it showing total sales. (I am using Date column from date table in both the visuals.)

Thanks for your responce.