"If" formula with "dates" considering various conditions

[dalmofm]

Helo Guys,

I work with insurance and some policies has different terms than a year (for example more similar to fiscal years: from April to April).

I'm struggling to develop this dax:

Term = if('Property - Historycal Claims (2015 - 2021)'[Claim Date]>=DATE(30/3/2015),"2015-2016",

if('Property - Historycal Claims (2015 - 2021)'[Claim Date]>=DATE(30/3/2016),"2016-2017",

if('Property - Historycal Claims (2015 - 2021)'[Claim Date]>=DATE(30/3/2017),"2017-2018",

if('Property - Historycal Claims (2015 - 2021)'[Claim Date]>=DATE(30/3/2018),"2018-2019",

if('Property - Historycal Claims (2015 - 2021)'[Claim Date]>=DATE(30/3/2019),"2019-2020",

if('Property - Historycal Claims (2015 - 2021)'[Claim Date]>=DATE(30/3/2020),"2020-2021",

if('Property - Historycal Claims (2015 - 2021)'[Claim Date]>=DATE(30/3/2021),"2021-2022","2022-2023")

The result of the entire column is only the first value: "2015-2016", but I have data until 2020.

The Claim Date column is settled as "date".

[mahoneypat · Accepted solution]

You could rewrite this simpler with SWITCH(TRUE(), ... but the issue is that all your date are >= 3/30/2015 so all meet the condition and get "2015-2016".  Reverse all your IF statements (>=3/31/2021 first) to get your desired result.

DAX - The Diabolical Genius of “SWITCH TRUE” - P3 (powerpivotpro.com)

Pat

[dalmofm]

Thank you Sir,

This is the formula that works:

Vigência = switch(

true(),

'Patrimonial - Histórico de Sinistros (2015 - 2021)'[Data do Sinistro] < date(2016,3,30), "2015-2016",

'Patrimonial - Histórico de Sinistros (2015 - 2021)'[Data do Sinistro] < date(2017,3,30), "2016-2017",

'Patrimonial - Histórico de Sinistros (2015 - 2021)'[Data do Sinistro] < date(2018,3,30), "2017-2018",

'Patrimonial - Histórico de Sinistros (2015 - 2021)'[Data do Sinistro] < date(2019,3,30), "2018-2019",

'Patrimonial - Histórico de Sinistros (2015 - 2021)'[Data do Sinistro] < date(2020,3,30), "2019-2020",

'Patrimonial - Histórico de Sinistros (2015 - 2021)'[Data do Sinistro] < date(2021,3,30), "2020-2021",

"2021-2022"

)