How to count records after applying a filter

[marypal]

Hello,
Could you please help me to write DAX formula to calculate the number of deals that skipped stages in the reporting period. The logic is the following.

We have a table deal_stage_hist, that contains a history of stages for each deal. last_modified_date is a date when a deal goes to the next stage.

Our reporting period is 01/01/2024 - 31/01/2024.
We also have stages_info table that contains information about stages and their order.

Sometimes a deal can skip a stage or can return to a previous stage.
From deal_stage_hist we can see that:
- Deal1 goes to stage4 on 29/01/2024 (that is within our reporting period). This means that in our reporting period this deal skipped stage0, stage1, stage2, stage3.
- Deal2 goes to stage6 on 22/01/2024 (that is within our reporting period). This means that in our reporting period this deal skipped stage0, stage1, stage2, stage3, stage4, stage5.
- Deal3 goes to stage1 on 29/01/2024 (that is within our reporting period). This means that in our reporting period this deal skipped stage0.
- Deal4 goes to stage5 on 22/01/2024 (that is within our reporting period). This means that in our reporting period this deal skipped stage0, stage1, stage2, stage3, stage4.
- Deal5 goes to stage0 on 22/01/2024 and then to stage3 on the same date (that is within our reporting period). This means that in our reporting period this deal skipped stage1, stage2.

I need to count for each deal a number of skipped stages for the reporting period.

I am a newby in Power BI, so I am asking for help...
I created the following measure:

Count Deals with Skipped Stages =

VAR MinModifDateInRepPeriod = CALCULATE(

                                        MIN(deal_stage_hist[last_modified_date]),

                                        KEEPFILTERS(DATESBETWEEN(deal_stage_hist[last_modified_date],[Reporting Period Start Date], [Reporting Period End Date]))

                                        )

VAR InputStage = CALCULATE(

                            MIN(deal_stage_hist[deal_stage]),

                            deal_stage_hist[last_modified_date]=MinModifDateInRepPeriod

                            )

VAR MinStageOrderInRepPeriod = CALCULATE(

                                        MIN(stages_info[stage_display_order]),

                                        stages_info[stage_id]=InputStage

                                        )

VAR CreatedStages = DISTINCT(

                        CALCULATETABLE(

                            VALUES(stages_info[stage_display_order]),

                                FILTER(

                                    deal_stage_hist,

                                    deal_stage_hist[last_modified_date] >= [Reporting Period Start Date] &&

                                    deal_stage_hist[last_modified_date] <= [Reporting Period End Date]

                                )

                        )

                    )

RETURN

    CALCULATE(

        DISTINCTCOUNT(deal_stage_hist[deal_id]),

        VALUE(stages_info[stage_display_order])<MinStageOrderInRepPeriod

        &&

        NOT stages_info[stage_display_order] IN CreatedStages

        )

The return value does not work. Could you please help me to write the correct DAX formula for this example?

Many thanks!

[lbendlin]

you cannot measure a measure directly, you need to materialize it first.  Usually via ADDCOLUMNS(SUMMARIZECOLUMNS())

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[marypal]

Hello @lbendlin ,

Thank you very much for your response.

I have created the "skipped_stages.pbix" file and the "skipped_stages.xlsx" file. The .pbix file contains 3 datasets: deal_stage_hist_sample (similar to the one mentioned in the question above), deal_stage_hist and stages_info. A simple model has been created. 
The "skipped_stages.xlsx" file contains information about the deal_stage_hist dataset, and the calculations and expected results for the periods September 2023, October 2023, November 2023, December 2023, and January 2024. There is a tab named "sample" that contains information about the calculation and the expected result for a small dataset deal_stage_hist_sample.

If there are any issues with opening the file, please let me know.
skipped_stages.xlsx
skipped_stages.pbix 

Thanks a lot.

[lbendlin · Accepted solution]

I need to count for each deal a number of skipped stages for the reporting period.

Is that all or do you need a row sum as well?

Note the DQ issue for Deal60

see attached

[marypal]

Hello @lbendlin ,

Thank you very much for your help!

Apologies, perhaps I did not explain the task in enough detail... In my Excel file  I provoded the calculation. If we consider September 2023 as a reporting period and look at Deal27, for example, we can see that stage6 was created. I flagged created stages as v, and skipped as 1. This means that we consider that stage0, stage1, stage2, stage3, stage4, stage5 were skipped. For Deal45 we see that stage0 and stage6 were created druring the reporting period, we consider that stage1, stage2, stage3, stage4, stage5 were skipped. For Deal48 stage0 and stage5 were created during the reporting period, so we consider that stage1, stage2, stage3, stage4 were skipped. For Deal49 stage0 was created, and as we do not have any stages before this one, we consider that for this deal there are no stages that were skipped. For Deal60 we have a situation when stage0 and stage5 were created twice during the reporting period. Such situations can occur. 

We consider not created stages during the reporting period, but only skipped stages: stages between created stages or before created stages.
 

I mentioned in my question "I need to count for each deal a number of skipped stages for the reporting period". Could you please tell me if it is possible to display the result as "Number of deals that skipped the particular stage"? For the below table (reporting period = Sep 2023) we have:
- there are 15 deals that skipped stage0 (we sum up only 1s for stage0)
- there are 38 deals that skipped stage1 (we sum up only 1s for stage1)
...
- there are 15 deals that skipped stage5 (we sum up only 1s for stage5)
As stage6 is the last one, we do not consider this stage as skipped.


Thank you!

[lbendlin]

I tihink your date range filter is problematic as you don't really know of stages were really skipped, or if they just happen to be outside of the filter range.

[marypal]

Hello @lbendlin 

It may sound strange, but this is the logic of the report.

We filter only the last_modified_date that falls within the reporting period and consider these stages as the starting point. For example, for Deal27, stage0 was created on 19.05.2023 (last_modified_date is 19.05.2023), stage3 was created on 02.06.2023, and stage6 was created on 29.09.2023.

If we consider 01.06.2023 - 30.06.2023 as the reporting period, then we see that only stage3 was created within that period (we look only back, not in the future). So, stage3 is the starting point, and we consider that stage0, stage1, and stage2 were skipped for the reporting period 01.06.2023 - 30.06.2023 (although in reality we see that stage0 was created on 19.05.2023 outside the reporting period).

If we consider 01.09.2023 - 30.09.2023 as the reporting period, then we see that only stage6 was created within that period. So, stage6 is the starting point, and we consider that stage0, stage1, stage2, stage3, stage4, and stage5 were skipped for the reporting period 01.09.2023 - 30.09.2023 (although in reality we see that stage0 was created on 19.05.2023 and stage3 was created on 02.06.2023).

If we consider Deal39 and the reporting period 01.10.2023 - 31.10.2023, we see that in October 2023 stage1 was created on 11.10.2023, stage5 was created on 11.10.2023, and again stage1 was created on 11.10.2023 (at different times of the day, which seems to be incorrect, but we have such cases in reality), and then stage5 was created on 23.10.2023. This means we consider all stages before stage5, excluding stage1 (because it was created in October 2023), to be skipped. So, stage0, stage2, stage3, and stage4 are considered to be skipped for the period 01.10.2023 - 31.10.2023.

[lbendlin]

I cannot agree with the premise. In our company we use field event history data and Sankey charts to describe what really happened.

I hope someone else can help you further.

[marypal]

Hi @lbendlin 
I had no chance to reply sooner, but now I want to express my gratitude for your help! I made some adjustments to your formula and achieved the desired result. Here is the solution.