Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated column with group value based on effective date

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 usin...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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?