Dynamic Summarize/Filter Formula

[Kreator]

I'm working on visualizing the updated user journey, and I've come up with a formula that provides accurate results when the full date range is selected. However, I've noticed that the formula doesn't adapt correctly when the date range is narrowed down. Any insights would be greatly appreciated.

Formula
COUNTROWS(   

    SUMMARIZE(

        FILTER(

            'Table',

            'Table'[date] = CALCULATE(MAX('Table'[date]), ALLEXCEPT('Table', 'Table'[Customer_ID], 'Table'[Journey_ID]))

        ),     

        'Table'[Customer_ID]  

    )

)

Formula Explanation
I'm filtering the table to have the max date per each customer and journey, then I'm counting the customers, the missing part is to have the max dates that are within the selected date range in the slicer

The case that works (only yellow lines are shown when full date range is selected)


The case that I need help with (if only grey dates are selected I want to show the green lines only)

[Anonymous · Accepted solution]

Hi @Kreator

Based on your description, please try the following steps:

1. Create a calculated table as follows.

Date = VALUES('Table'[Date])

2. Create several measures as follow.

MaxDate = 
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Journey_ID], 'Table'[Customer_ID] ),
        [Date] >= MIN ( 'Date'[Date] )
            && [Date] <= MAX ( 'Date'[Date] )
    )
)

Measure = IF(SELECTEDVALUE('Table'[Date]) = [MaxDate], 1, 0)

count = COUNTROWS(SUMMARIZE(FILTER('Table', [Date] = [MaxDate]),'Table'[Journey_ID]))

3. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.