Dax Ranking Measure 2nd sale & 3rd sale

DAX DAX Calculations
1

Hi All,

Scenario is as below -

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 -

image
image1159×454 20.1 KB

Sample data is attached
Sample report.pbix (52.9 KB)

@BrianJ , @AntrikshSharma - It would be great , if you can help here

2

@Vishy ,

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 hope this is helpful.

  • Brian
2 Likes
3

Hi @BrianJ -

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.

So is the struggle :slight_smile:

4

@Vishy ,

Would it be acceptable to create a referenced, transformed table in PQ separate from the main fact table?

  • Brian
5

@BrianJ - Actually I had asked for the same , but actually that is not allowed as of now. However is it possible to create a Calculated Table using Dax in Model itself and work on the ranking Part.

As with Direct Query cube mode the PQ doesnt give you any table

image
image1917×1046 48 KB

So if we can use the route of creating a Calculated Table in Data model using Dax and work on the same ?

7

Hi @Vishy

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.