Contract ending next 6 months and 12 months and beyond

[AnnaShenHO]

Contract title	Contract End Date	Contracts Ending in 6 or 12 mths
A	1st Dec 2024	Contract Ending in 6 mths
B	2st Jan 2025	Contract Ending in 6 mths
C	1st June 2025	Contract Ending in 12 mths
D	31st Dec 2025	Contract Ending Beyong 12 mths

Dear team, I have existing 2 columns (Contract Title and End Date). I'm trying to add a new columns, similiar like

"Contracts ending in 6 or 12 mths", each if [End Date] <= today's date + 6 months then "Contracts ending in 6 mths" else if [End Date] <= today's date + 12 months then "Contracts ending in 12 mths" else "Contracts ending after 12 mths").

I just dont know how to write dax about above underlines part. Grateful for your advise. 

Anna

[v-kaiyue-msft · Accepted solution]

Hi @AnnaShenHO ,

Your solution is great, DataInsights . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference. 

You can try the following calculated column.

Contracts Ending in 6 or 12 mths = 
VAR _today = TODAY()
VAR _diff = DATEDIFF(_today,'Table'[Contract End Date],MONTH)
RETURN
SWITCH(TRUE(),
_diff <= 6, "Contract Ending in 6 mths",
_diff <= 12, "Contract Ending in 12 mths",
"Contract Ending Beyong 12 mths")

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[DataInsights]

@AnnaShenHO,

Try this calculated column:

Contracts Ending in 6 or 12 mths = 
VAR vToday =
    TODAY ()
VAR vContractEndDate = 'Table'[Contract End Date]
VAR vResult =
    SWITCH (
        TRUE,
        vContractEndDate <= EDATE ( vToday, 6 ), "Contracts Ending in 6 mths",
        vContractEndDate <= EDATE ( vToday, 12 ), "Contracts Ending in 12 mths",
        "Contracts Ending Beyond 12 mths"
    )
RETURN
    vResult