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

     

     

EWBWEBB's avatar
EWBWEBB
Icon for Helper III rankHelper III
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