cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
avelio Regular Visitor
Regular Visitor

Prior Year Sales Data does not work when applying more then one filter.

Dears,

 

Please advise on the issue below.

 

I have created 3 measures in order to display the % change of sales when selecting a year and it goes like this:

 

1) I've created a measure to calculate total sales: Total Selected Sales = sum('to'[Turnover])

2) I've created a measure to calculate Prior Year Sales:

Prior Year Sales =
VAR
selectedYear = if( HASONEVALUE('DateKey'[year]),
FIRSTNONBLANK(DateKey[year],DateKey[year]),
BLANK() )-1
Return
CALCULATE(
SUM('to'[Turnover]),
ALL('to'),
DateKey[year] = selectedYear)

3) A measure which displays the increare/decrease in %: Percent Change = DIVIDE([Total Selected Sales],[Prior Year Sales],1)-1

 

Everything works fine when i'm applying a Year filter as you can see below.

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When i'm also applying a filter to a lowest level, (brand), to check the sales for a specific shop in a shopping mall, the Prior Year Sales remains the same.

Capture.JPG

 

 

 

I can't understand why Prior Year Sales doesn't apply to the Brand level (the information is in the same table, it's not a relationship problem).

 

Any ideas?

 

 

 

 

 

BR,

Andrei

1 ACCEPTED SOLUTION

Accepted Solutions
mattbrice Senior Member
Senior Member

Re: Prior Year Sales Data does not work when applying more then one filter.

In your CALCULATE function call, the “ALL(‘to’)” is blocking the filter on Brand.  From what I see it should be removed.  

View solution in original post

3 REPLIES 3
mattbrice Senior Member
Senior Member

Re: Prior Year Sales Data does not work when applying more then one filter.

In your CALCULATE function call, the “ALL(‘to’)” is blocking the filter on Brand.  From what I see it should be removed.  

View solution in original post

avelio Regular Visitor
Regular Visitor

Re: Prior Year Sales Data does not work when applying more then one filter.

Hi Matt,

 

Thanks for your reply.

Tried removing it, still having the same result.

 

thanks

avelio Regular Visitor
Regular Visitor

Re: Prior Year Sales Data does not work when applying more then one filter.

On second thought, it works.

My mistake!

 

thanks a lot!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 63 members 1,407 guests
Please welcome our newest community members: