Calculation of distinct IDs for associated Max date and flag

[TejasMahind]

Hello all,

I'm trying to solve a business problem which has mutliple dates and filter combinations for generating the output. Below is the sample of my dataset, 

User has the ability to filter on the raw date and we have other filters for product, company, salesperson on the page

Doubt: 
1. When a user selects a date range in the filter ( Suppose 1/1/2023 - 6/1/2023 as per above image), we will extract the MAX (Edit date) i.e. (3/30/2023) corresponding to the filtered time period and check if the Flag value is 1 for the MAX edit date. If yes, we will be calculating the DISTINCT COUNT of such IDs as the main output (1)

2. When a user selects date range in the filter and select a product, then we will extract the MAX (Edit date) corresponding to the filtered data and check if the Flag value corresponding to MAX edit date is 1. In the below scenario, user has filtered the date and selected 'Tablets' in product, for this selection the MAX edit date is 4/30/23 and the corresponding flag is 0. Therefore this ID would not be getting counted in our main output (1)

3. Scenario: 
When a user selects a date range in filter, the output (1) should be generated as per above logic. Also, we should be looking up for same period in previous year, checking the MAX(Edit date) for this interval and check if we have Flag = 1 corresponding to this date. If yes we should DISTINCT COUNT such IDs as Output 2
Please find below scenario sample, 

User has selected 4/1/2023 to 6/1/2023 in the date filter, for this window MAX(Edit date) is 4/30/2023, but the corresponding flag for this date is 0 therefore the DISTINCT COUNT of such IDs will be 0 (output 1) . For Output 2, we use the same time date range of previous year ( un highlighted raw dates) and see if the MAX(Edit date) i.e. 8/1/2022 in this case has Flag =1. If yes, we will be counting DISTINCT COUNT of such IDs as Output 2. 

Below is the link for pbix file with sample data. Request to please share your thoughts ideas for the same. Let me know if any additional clarification is required. Thanks!

PBIX file link : https://drive.google.com/file/d/1bEcp6YLluaQXnGcMALpW-usKn7vjW1YH/view?usp=sharing 

[lbendlin]

Your sample data has multiple rows for the edit dates. 

Please verify,

[TejasMahind]

Hi @lbendlin ,
I have modified the dataset and have created some measures also. Please let me know if anything else is required. 
New PBIX file : https://drive.google.com/file/d/1iRZMjsQDU2oWCjn62uM6XjZzcpc1lbqf/view?usp=sharing

[lbendlin]

[TejasMahind123]

@lbendlin This works. Can you also help me with how can we further calculate distinct count corresponding to max edit date and the same for previous year?

[lbendlin]

For the distinct count you can use a measure that uses the above formula as a filter.  I don't quite understand how you are planning to compute  "the same for the previous year" - please elaborate.

[TejasMahind123]

Sure, can you please look at Scenario 3 in the query description I shared above? I have elaborated using an example