Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
avelio
Helper II
Helper II

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
mattbrice
Solution Sage
Solution Sage

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

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

On second thought, it works.

My mistake!

 

thanks a lot!

Hi Matt,

 

Thanks for your reply.

Tried removing it, still having the same result.

 

thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.