cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Urgent Help needed in Dynamic Ranking based on Date as a Measure.

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.

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Urgent Help needed in Dynamic Ranking based on Date as a Measure.

Really need to have data for this one. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Urgent Help needed in Dynamic Ranking based on Date as a Measure.

Hello @Greg_Deckler ,

 

Thanks for reply!

Will share sample data shortly.

 

Highlighted
Helper I
Helper I

Re: Urgent Help needed in Dynamic Ranking based on Date as a Measure.

Hi @Greg_Deckler ,

 

I have uploaded a sample data here.

https://uploadfiles.io/tus89lp8

 

Please let me know in case of any more information required.

Eagerly waiting for your help.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors