Forum Discussion

UK_User123456's avatar
UK_User123456
Resolver I
6 years ago

Gift Date that falls between two dates

Hi All,

 

I wanted to be able to find what categories a date I have in my data set falls into. So based on the end of each month, I would like to be able to use the end of the month to calculate 24/48/60/60+ months back and then if one of the dates falls into this bracket then it would put the category of 0-24 and then the same with 25-48 and so on, my data set looks like below:

 

Date31/10/2019
  
Gift DateCategory
30/10/20190-24
21/01/20190-24
01/05/201825-48
24/01/200860+

 

Any help would be much appreciated.

 

TIA

Need Help

1 Reply

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    Hi UK_User123456 ,

     

    First, you can define a base date

     

    BaseDate = DATE(2019,10,31)

     

    Now you can create a calculated column. 

     

    Category = 
VAR difference =
    DATEDIFF ( 'Table'[Gift Date], [BaseDate], MONTH )
VAR category =
    IF (
        difference >= 0
            && difference <= 24,
        "0-24",
        IF (
            difference >= 25
                && difference <= 48,
            "25-48",
            IF (
                difference >= 49
                    && difference <= 60,
                "49-60",
                IF ( difference > 60, "60+", "" )
            )
        )
    )
RETURN
    category

     

    Output

     

    Regards,

    Nandu Krishna