Gift Date that falls between two dates

[UK_User123456]

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:

Date	31/10/2019
Gift Date	Category
30/10/2019	0-24
21/01/2019	0-24
01/05/2018	25-48
24/01/2008	60+

Any help would be much appreciated.

TIA

[nandukrishnavs]

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