cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Highlighted
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
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,026)