cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors
Users online (5,268)