Calculated column with group value based on effective date

[Anonymous]

Hello, 

I am looking for a way to create a calculated column that matches the Date / Category and Value against set bands in the Category Bands with the Effective Date table. Currently, I am using Filter and IF statements to get the desired result but were hoping to find a more elegant solution. 

Could anyone please advice me on the best approach?

Category Values

Date	Category	Value
02/01/2021	A	9
03/01/2021	B	400
04/01/2021	C	100
05/01/2021	D	9
25/02/2021	A	60
25/02/2021	B	100
25/02/2021	C	30
25/02/2021	A	10

Category Bands with Effective Dates

Category	1st Class	2nd Class	3rd Class	Effective Date
A	>100	100-50	<50	01/01/2021
B	>200	200-70	<70	01/01/2021
C	>50	50-10	<10	01/01/2021
D	>10	10-5	<5	01/01/2021
A	>50	50-10	<10	20/02/2021
B	>10	10-5	<5	20/02/2021
D	>10	10-5	<5	20/02/2021

Desired Outcome

Date	Category	Value	Class
02/01/2021	A	9	3rd
03/01/2021	B	400	1st
04/01/2021	C	100	1st
05/01/2021	D	9	2nd
25/02/2021	A	60	1st
25/02/2021	B	100	2nd
25/02/2021	C	30	2nd
25/02/2021	A	10	2nd

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Please refer this formula.

Column =
VAR _Effective =
    CALCULATE (
        MAX ( 'Category Bands'[Effective Date] ),
        FILTER (
            'Category Bands',
            'Category Bands'[Category] = 'Category Values'[Category]
                && 'Category Bands'[Effective Date] <= 'Category Values'[Date]
        )
    )
VAR _1st =
    CALCULATE (
        MAX ( 'Category Bands'[1st Class] ),
        FILTER (
            'Category Bands',
            'Category Bands'[Category] = 'Category Values'[Category]
                && 'Category Bands'[Effective Date] = _Effective
        )
    )
VAR _max =
    MID ( _1st, 2, 9999 ) + 0
VAR _3rd =
    CALCULATE (
        MAX ( 'Category Bands'[3rd Class] ),
        FILTER (
            'Category Bands',
            'Category Bands'[Category] = 'Category Values'[Category]
                && 'Category Bands'[Effective Date] = _Effective
        )
    )
VAR _min =
    MID ( _3rd, 2, 9999 ) + 0
RETURN
    SWITCH (
        TRUE (),
        'Category Values'[Value] > _max, "1st",
        'Category Values'[Value] < _min, "3rd",
        "2nd"
    )

Best Regards,

Jay

[Jihwan_Kim]

Hi,

I suggest having a category table like the below structure.

Please check the below picture and the attached pbix file.

It is for creating a new column.

Class CC =
VAR currentcategory = Data[Category]
VAR currentdate = Data[Date]
VAR effectivecategorydate =
    MAXX (
        FILTER (
            Category,
            Category[Category] = currentcategory
                && Category[Effective Date] <= currentdate
        ),
        Category[Effective Date]
    )
VAR effectivecategorytable =
    FILTER (
        Category,
        Category[Category] = currentcategory
            && Category[Effective Date] = effectivecategorydate
            && Data[Value] >= Category[Min]
            && Data[Value] <= Category[Max]
    )
RETURN
    MAXX ( effectivecategorytable, Category[Class] )

[Anonymous]

Hi, 

Great solution, yet I am unable to change the structure of the category table as it has thousands of rows. 

Instead I came up with the solution below:

1. Created Effectiveness End date in the "Category Bands with Effective Dates" table. 

2. Added this column to "Category Values".

Class =

VAR 1st Class = CALCULATE(AVERAGE('Category Bands with Effective Dates'[1st Class]),

FILTER('Category Bands with Effective Dates',

'Category Bands with Effective Dates'[Effective Date]<='Category Values'[Date] &&

'Category Bands with Effective Dates'[Effective End Date] >= 'Category Values'[Date] &&

'Category Bands with Effective Dates'[Category]='Category Values'[Category]

)

)

repeated this for other Classes and added IF statementes to return the Band Class.

Is there a simpler solution for the given table structure?