Forum Discussion

EWBWEBB's avatar
EWBWEBB
Icon for Helper III rankHelper III
2 years ago
Solved

Slicer affecting measure

Hi   I am trying to count the total number of people that joined the business in the last two years.   I want to be able to use a slicer to select the current reporting period and then return all...
  • EWBWEBB's avatar
    EWBWEBB
    2 years ago

    Hi All

    Took a different approach and got the desired result - also alot simpler.

     

    TotalNotMaleJoiners (Heads) L2Y = 
VAR MaxDate = 
MAX('DateDim'[FullDate])

VAR CurrentYear =
   YEAR(
    MAX('DateDim'[FullDate])
   )

VAR Jan12YearsPrior = DATE(CurrentYear - 2,1,1)
    

VAR Joiners = 
CALCULATE(
    [TotalNotMaleJoiners (Heads)],
    FILTER(
        ALL('DateDim'), 'DateDim'[FullDate] >= Jan12YearsPrior &&
         'DateDim'[FullDate] <= MaxDate)
)

RETURN
Joiners

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

I think

 VAR SelectedYear = 
 YEAR(  MAX(  'DateDim'[Fulldate] ) )

should work just fine to start with. 
By using the all function for your date table you remove it's filters, but that's not what you want in this case?

Good luck!

  • EWBWEBB's avatar
    EWBWEBB
    Icon for Helper III rankHelper III
    2 years ago

    Hey

     

    That did work, thank you. 

     

    However when Apply the slicer to select the relevant period it seems to filter the measure.

    The period slicer relates to about 20 dates, so I want to take the max of those days for the end date in the dates between. But I think what it is doing is over-riding the start date?