I want to calculate the Internal Ranking based on reg date ,Second Sale Flag and Third Sale flag. for a particular year , If a agent makes a sales for consecutive two month then the 1st record of the 2nd month is flagged as 2nd sale and if a agent makes a sales for consecutive 3 months then the 1st record of the 3rd month is marked as third sale within a year Expected out put is as below for sample data -
Whenever you’re dealing with calculating consecutive streaks, it is MUCH easier to do those in Power Query/M than in DAX. There’s an option in M called GroupKind.Local for automatically calculating these exact types of streaks.
If you need more specific direction on how to apply this, please provide the Excel file with the data, and I can write the specific M code to get the result you need.
I have shared the pbix with the data model and data.
However here is the challenge the original data model that I have is basically a direct query from production cube . So the goal here is not to make any changes in the cube but still implement the above logic by creating measures or calculated column.
Thank you for your participation in our forum. We noticed that your question has been stale for quite some time now.
To ensure that you receive the best possible assistance, we recommend creating a new forum post with an updated status of your issue. This will help our members and experts understand the current situation and provide more accurate and effective solutions.
When creating a new post, please be sure to provide as much detail as possible
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)
This will help our members and experts provide a more targeted solution to your needs.
Thank you again for your participation in our community, and we look forward to seeing your new post.