cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 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/2021A9
03/01/2021B400
04/01/2021C100
05/01/2021D9
25/02/2021A60
25/02/2021B100
25/02/2021C30
25/02/2021A10

 

Category Bands with Effective Dates

 

Category     1st Class     2nd Class     3rd Class     Effective Date     
A>100100-50<5001/01/2021
B>200200-70<7001/01/2021
C>5050-10<1001/01/2021
D>1010-5<501/01/2021
A>5050-10<1020/02/2021
B>1010-5<520/02/2021
D>1010-5<5

20/02/2021

 

Desired Outcome

 

Date     Category      Value      Class     
02/01/2021A93rd
03/01/2021B4001st
04/01/2021C1001st
05/01/2021D92nd
25/02/2021A601st
25/02/2021B1002nd
25/02/2021C302nd
25/02/2021A102nd
1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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"
    )

1.jpg

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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"
    )

1.jpg

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Jihwan_Kim
Super User
Super User

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.

 

Picture5.png

 

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] )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

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?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.