cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
atul-patil
Helper V
Helper V

Last Week sales not filtering well

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:
WOW.png
Please help me.
 
Thanks
 
1 ACCEPTED SOLUTION
atul-patil
Helper V
Helper V

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.

View solution in original post

3 REPLIES 3
atul-patil
Helper V
Helper V

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.

View solution in original post

amitchandak
Super User IV
Super User IV

@atul-patil , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

WOW1.png

Thanks for your responce.

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors