cancel
Showing results for
Did you mean:
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.

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
1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

``````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

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

Hi @Anonymous ,

``````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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
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.

``````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.

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?

Announcements

#### 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.

#### 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!

#### Business Application LATAM Summit 2023

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

#### 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.

Top Solution Authors
Top Kudoed Authors