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

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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?
  • Anonymous's avatar
    Anonymous
    Not applicable
    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