Hi All,
I have created a report that allows me to slice by week based on the dax formula below which filters by week. However I want a way to toggle between slicing by week and slicing by month &year (So when I slice by week I want to see only weekly numbers and when I slice by month only monthly numbers). Here is the formula I have tried in the Return below but it does not work and your help will be very much appreciated. Thanks in advance for your help.
No of Active Employees (VP - Month Filter) =
VAR Weekending =
SELECTEDVALUE ( FYCalendar[WeekEnding] )
VAR FYMonthnYear =
SELECTEDVALUE ( FYCalendar[Month & Year] )
VAR ActiveEmployees =
FILTER (
tkMaster,
OR (
RELATED ( EMAllCompany[TerminationDate] ) = BLANK (),
OR (
RELATED ( EMAllCompany[TerminationDate] ) >= Weekending,
RELATED ( EMAllCompany[TerminationDate] ) >= Weekending - 6
)
)
) //Employees whose term date is blank or greater than or equal to WE
VAR Leavers =
FILTER (
tkMaster,
OR (
RELATED ( EMAllCompany[Date of Return] ) = BLANK (),
RELATED ( EMAllCompany[Date of Return] ) <= Weekending
)
) // Remove those on Leavers list based on date of return
VAR HiredatelessthanorequalWE =
FILTER ( tkMaster, RELATED ( EMAllCompany[HireDate] ) <= Weekending ) // Hiredate has to be less than or equal to the week ending period
VAR RemovePersonA =
FILTER (
tkMaster,
RELATED ( EMAllCompany[Employee Name] ) <> “PersonA”
) //Contractor does not required timesheets filled in
VAR RemovePersonB =
FILTER ( tkMaster, RELATED ( EMAllCompany[Employee Name] ) <> “PersonB” ) //Contractor does not required timesheets filled in
VAR RemovePersonC =
FILTER (
tkMaster,
RELATED ( EMAllCompany[Employee Name] ) <> “PersonC”
) //Non Exec Chair does not require timesheets filled in
VAR RemovePersonD =
FILTER ( tkMaster, RELATED ( EMAllCompany[Employee Name] ) <> “PersonD” ) //Non Exec Chair does not require timesheets filled in
VAR CountryUK =
FILTER ( VALUES ( tkMaster[HomeCountry] ), tkMaster[HomeCountry] = “UK” ) //Filtering onyly UK in the tkMaster table to eliminate PersonE duplicates in France and UK
VAR CountryUK2 =
FILTER (
VALUES ( EMAllCompany[HomeCompany] ),
EMAllCompany[HomeCompany] = “UK”
) //Filtering Only UK in EMallCompany table to just include UK persons
VAR NoofemployeesWE =
CALCULATE (
[No of Employees (VP)],
ActiveEmployees,
Leavers,
HiredatelessthanorequalWE,
RemovePersonA,
RemovePersonA,
RemovePersonC,
RemovePersonC,
CountryUK,
CountryUK2
)
VAR NofemployeesbyMonthnYear =
CALCULATE (
NoofemployeesWE,
ALLEXCEPT ( FYCalendar, FYCalendar[Month & Year] )
)
RETURN
IF (
SELECTEDVALUE ( FYCalendar[WeekEnding] ) = Weekending,
NoofemployeesWE,
IF (
SELECTEDVALUE ( FYCalendar[Month & Year] ) = FYMonthnYear,
NofemployeesbyMonthnYear
)
)
Thanks
P