Dax query using calculated buckets as filters

Hi Anonymous

I agree Dynamic Segmentation is the way to go.

Try this adjusted version of your measure:

Segments Test =
IF (
    ISFILTERED ( Segments[Name] ),
    VAR MaxDate = [MaxSelectedDate]
    RETURN
        CALCULATE (
            CALCULATE ( COUNT ( Orders[OrderNumber] ), Orders[IsOpen] = 1 ),
            FILTER (
                VALUES ( Orders[dateCreated] ),
                COUNTROWS (
                    FILTER (
                        Segments,
                        DATEDIFF ( Orders[dateCreated], MaxDate, WEEK ) >= Segments[MinValue]
                            && DATEDIFF ( Orders[dateCreated], MaxDate, WEEK ) < Segments[MaxValue]
                    )
                )
                    > 0
            )
        ),
    CALCULATE ( COUNT ( Orders[OrderNumber] ), Orders[IsOpen] = 1 )
)

I have made a few changes highlighted in red.

The critical one is to filter VALUES ( Orders[dateCreated] ) rather than VALUES ( Orders[OrderNumber]), since we want to iterate through each value of dateCreated value and determine whether it is included or excluded. The previous MIN/MAX approach could have worked as well if they were wrapped in CALCULATE, but I think this way is simpler.

Does this give the right result?

Regards,

Owen