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

     

     

sjoerdvn's avatar
sjoerdvn
Icon for Solution Sage rankSolution Sage
2 years ago

something like

TotalNotMaleJoiners (Heads) L2Y = 
    VAR PeriodMaxDate = MAX('DateDim'[FullDate])
    VAR Jan1TwoYearsAgo = DATE(YEAR(PeriodMaxDate)  - 2,1,1)
    VAR  VAR DateRange = CALCULATE(DATESBETWEEN('DateDim'[FullDate],Jan1TwoYearsAgo,PeriodMaxDate), ALL('DateDim'))
    RETRN CALCULATE(
        [TotalJoiners (Heads)]
        ,'PersonDim'[Gender] <> "Male"
        ,ALL('DateDim')
        ,'DateDim'[FullDate] IN DateRange
    )