Categorise data by expiry date

[donodackal]

Hello

I have got a table that contains contract expiry dates. Would like to create a new column that identifies contracts as

• Expired
• Expiring in 90 Days
• Expiring in 180 Days
• Expiring 180 Days + 

My final output is creating a matrix table that counts the number of contracts falling in the four categories above. 

Can anyone help with the steps to get this output?

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Contracts count: =
CALCULATE (
COUNTROWS ( VALUES ( Data[Contract] ) ),
FILTER (
Data,
CALCULATE (
COUNTROWS ( Category ),
FILTER (
Category,
Data[Expiry date] - TODAY () >= Category[Min period]
&& Data[Expiry date] - TODAY () <= Category[Max period]
)
) > 0
)
)

[ryan_mayu]

@donodackal 

pls  try this

category = 
VAR gap='Table'[expiry date]-TODAY()
return SWITCH(TRUE(),gap<0,"expired",gap<=90,"Expiring in 90 Days",gap<=180,"Expiring in 180 Days","Expiring 180 days +")

[donodackal]

Hi Ryan, 

Tried the formula but it throws up errors, am I entering it correctly?

[ryan_mayu]

@donodackal 

try to use DAX to create a column, not in the PQ editor.