Hello Dax Experts,
I need your urgent help!
First let me explain you about data.
I have a EmployeeTable. It consist of ID, EmployeeID , AssignementID, StartDate, EndDate, ExtensionNumber, TypeOfAssignment(Perm/Contract), HiredByOrganization, Profit/Loss. For employee, assignments also got extended. So for them there are multiple entries for employee for same assignment.
Below is the user requirement:
User will select a SingleDate. The complete report should be running for that date. Whichever date is selected only the records which exist under below criteria should be considered.
StartDate> SelectedDate-60 (Should not be more then 60 days old from SelectedDate)
EndDate>=SelectedDate (Only non expired/finished Assignments)
For this I have created standalone table consisting date. I am picking the value of Date given by User with the help of SelectedValue function.
I have created a measure with the help of SELECTEDVALUE and when the above criteria is getting satisfied I am giving value as 1 or else blank. And I am giving this measure as a filter in my matrix or other measure using FILTER dax i.e. [Measure]>0 to fulfil above requirement and it is working completely fine.
I need to show the count of Employees for each Organisation in 2 categories i.e AssignmentEndingin2weeks and AssignmentEndinginmorethen2weeks (Needs to be calculated as per the Selected Date by user and AssignementEndDate). Also I need to drag Profit/Loss column in column field of my matrix.
For this I need to take the count of the Emoloyees. The above two categories of AssignmentEnding duration in weeks also depend on the Date Selected by user.
So first we need to create a calculated column which will be concatination EmployeeID and AssignmentID. Now we need to Rank this calculated column based on a AssignmentEndDate in descending order so that I will give this as a FILTER wherever required i.e Rank=1. I need to create it as a measure. As everything depends on the Date Selected by user Calculated Coumn will not work.
But I also need to take care of below criteria before giving rank.
StartDate> SelectedDate-60 (Should not be more then 60 days old from SelectedDate)
EndDate>=SelectedDate (Only non expired/finished Assignments)
I first need to fulfill above criteria and after that need to Rank. And will use this measure as Filter i.e [Measure]>0 wherever required.
I am badly stuck need your urgent help.
Thanks in Advance.
