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.
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)
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.