Count of products this year versus last year

ruthhacche

Board Regular
Joined
Sep 22, 2017
Messages
84
I want to do a waterfall chart showing the movement in the number of products (selling more than £100) from last year to this year. So I need the number of products sold in two fiscal years in one visual which is filtered for just the current year.

I have a number of products measure

Number of Products =
CALCULATE (
DISTINCTCOUNT ( 'Order Lines'[Item Short Name] ),
FILTER ( 'Order Lines', [Sale] > 100 )
)

When the current fiscal year is selected this measure returns the number of product sold in the current fiscal year (YAY). It would be lovely if I could just change [Sale] to [Sale LY] but clearly it is not as simple as this. So please point me in the right direction to get Number of Products LY. I am sure it is simple for some of the brains on here.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top