Latest Enterprise DNA Initiatives

DAX formula to accomodate both by Weekending Period Slicing and by Month&Year Slicing

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

@BrianJ @Melissa @AntrikshSharma could you please help with above query. Thanks.

Hi,

Any chance of getting the pbix file so i can try to solve your problem.

Thanks,
Anurag

@Anurag Thanks for your reply. At the moment the data is confidential and it will be a mammoth task to anonymize them. I thought just by looking at the dax formula you can know where the issue is?

Hi @ambepat , I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your current work-in-progress PBIX file
  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

Thanks

Hi @ambepat, we’ve noticed that no response has been received from you since Sep 10.

We are waiting for the masked demo pbix file and any other supporting links and details. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.