Group by different column based on slicer value

[tcheong]

Hello, please refer to this pbix for this question.

Background:

I have the following data in the 'Products' table:

Id	CreatedDate	Grade	ModifiedDate	NewGrade
1	10 Nov 2020	A	10 Nov 2020	B
2	12 Nov 2020	A	16 Nov 2020	C
3	15 Nov 2020	B	19 Nov 2020	C
4	22 Nov 2020	B	30 Nov 2020	A

The values of [Grade] and [NewGrade] are always either "A", "B", or "C". 

There is also a 'DateDim' table with a one-to-many relationship to 'Products'.[CreatedDate]. A date range slicer is created for 'DateDim'.[Date]:

The Problem:

I want to know how many products are in each grade within the selected date range. However, if the max date in the date range slicer is >= [ModifiedDate], it should be grouped by the value in [NewGrade] instead of [Grade].

For example, the expected result for a date range between 10/11/2020 and 17/11/2020 should be:

Grade	Count
B	2
C	1

Expected results for a date range between 10/11/2020 and 22/11/2020 should be:

Grade	Count
B	2
C	2

How can I achieve these results?

[tcheong · Accepted solution]

Hello, I managed to achieve what I needed by creating a measure for each grade like so:

_ProductCount_GradeA = 
VAR slicerMaxDate = MAX(DateDim[Date])

RETURN
    CALCULATE(
        COUNT(Products[Id]),
        FILTER(
            ALL(Products[ModifiedDate], Products[Grade], Products[NewGrade]),
            IF(Products[ModifiedDate] <= slicerMaxDate, [NewGrade], [Grade]) = "A"
        )
    ) + 0

You may refer to this PBIX for my solution.

Thanks to everyone who took the time to consider my question. Have a nice day!