Dynamic column based on slicer

[PowerDonkey]

Hi Guys,

Been searching the forums but cannot find my specific requirement.   My report needs to look at new accounts that have sales per fiscal year.  The dataset spans the last 10 years which has been grouped into sales FY.  The challenge is that I need to select a slicer to choose the specific year and then show which sales were new accounts in that year.  For example

The "Sibling Year" is the first time that chain did business therefore it is classed a New Account if the sales year = Sibling Year. To make it more complex, the user is going to click on a year on a slicer so the logic needs to be <slicer year selection> = Sales Year = Sibling Year, so triple the fun!

For futher context, the above example a new chain of Dunkin Donuts opened in 2011 but this is not deemed a new account as its part of a chain which was established in 2010 (with East). 

As this dataset controls other viewpoints such as cumalitive graphs on the products I need to perform this in the same dataset. I am a bit of a newbie with DAX (Qlik Developer used to variables!) and know it is going to be something DAX related but cannot work it out.  Any help would be great.

Kind Regards,

PD.

[erik_tarnvik · Accepted solution]

First you need a paramteter table for the year selection slicer. Maybe you already have that, let's call it Years. Otherwise you can create it based on your Data table column Sales Year, assuming it contains all possible values for years. Click "New Table" and enter

Years = SUMMARIZE(Data, Data[Sales Year])

Make sure that this new table has no relationships. Next, create a measure:

CompareYears = 
IF(  NOT(HASONEVALUE(Years[Sales year])) || 
     MAX(Data[Sales year]) = MAX(Years[Sales year]) && 
     MAX(Data[Sibling Year]) = MAX(Years[Sales year]),
   1,
   0)

Create a slicer using Years. Then, for your table, drop CompareYears into the Visual Filter area and set the filtering to include values evaluated to 1. Done.

[PowerDonkey]

Hi Erik,

Thank you very much.  I did not consider a dax table as a variable for selection.  This works perfectly.