RANKX with dynamic date filter

I have a calculation that take a measure that counts the number of times each category is selected.

So basically a base measure that calculates the total counts of each category, and then another measrue that ranks each category based on the totals.

The rankx works great with a date filter/slider. I am trying to build the date filter inside this measure so I can create three separate measures of rankings. One that is governed by the date slicer, one that takes the max date of the slicer and then goes back 90 days and ranks, then the final one takes the max date and goes back 365 days and ranks the categories.

The reason for this is I want to put all three of these in one table that will display the rank of each category for the three time periods. I also want to take the average of the three rankings as a final ranking. This way I can display in ONE TABLE instead of currently having to do it in three separate tables with three separate date filters.

I unfortunately can’t provide a file due to the nature of my work and the data. Event if there is a great tutorial how to do this or even a basic format would be great.

The current two measures are as follows:

Total Drivers = COUNT(DataTable[driver])

Ranking =
IF(
[Total Drivers] <> Blank(),

RANKX(
ALLSELECTED((Table[Drivers]),
[Total Drivers],
,
DESC),
[Total Drivers])

1 Like

Hey,

Please drop pbix

Warm Regards,
Pankaj

As mentioned I am unable to attach a file as it is work related and confidential material.

1 Like

For the forum members to continue to pursue, please create and upload a PBIX that illustrates your issue that contains sample data only.
Greg

1 Like

Hi @ToddFetcho

as requested above, please provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

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

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

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

Thank you!

Hello @ToddFetcho

We notice that no response was received from you on the post above.

We are waiting for your masked demo pbix file and other supporting links and details. Other users can help you more if you have a PBIX file in your query.

Due to your inactivity, we’ll be tagging this post as Solved.

Should you wish to add your masked demo pbix file and other supporting links and details, you can reopen this thread.

I apologize for the late response. I had to go back and find a suitable dataset/pbix file to upload.
I can’t even send a masked file as our data governance laws won’t allow me to even send masked data outside of the company.

I did find a dataset to post here so there will be a slight modification to the request.

I want to create three measures. I want to calculate the rank of Channel by total number of orders.

The rank will be done across three time periods that are driven off a dynamic date filter.
First one is last 30 days from the max date of the date selected, 90 days from max date selected and one year from max date selected.

It should return the rank of the channel based on total orders during the time period.
A final measure would be to take the rankings of all three of the rankx measures and provide an average rank for each channel across the three time periods.

Ultimately I want to put all three measures in a table and have it dynamically change based on a date filter.

Mastering+DAX+Calculations±+Base+File.pbix (1.6 MB)

I enclosed a file that has enough date data and a suitable category to do this.

Thank you to all for helping me with this. And I again apologize for the delayed response.

Bumping this post for more visibility from our experts and users.

Hi @ToddFetcho,

I noticed that this forum thread has been inactive for a while.

I suggest that we close this thread and create a new one with the updated details you provided.

This will allow our members and experts to easily access the most up-to-date information without having to scroll through the entire thread.

I will go ahead and close this thread now, and I look forward to seeing your new post.

Thank you!

It was reopend and bumped.