Compare year and date of 2 date only fields

[ttocso]

Hi,

I have a calculated column where i am calculated the risk based on a number of variables. I would like to change this slightly so that in addition to the below, if the month and year of the 2 dates are equal, i would set the risk to medium. The 2 dates to compare are both date only formatted fields (Gantt_date and FISCAL_FIRST_OF_MONTH). 

If('Jira Status'= "Closed", "Available",

If('Jira Status'= "Resolved", "Available",

If(Not(IsBlank(HW_only)), "Available",

If(Not(IsBlank(Solution_available)), "Available",

If(Gantt_trend_date>FISCAL_FIRST_OF_MONTH,"High",

If(Gantt_trend_date<FISCAL_FIRST_OF_MONTH,"Low",

If(Gantt_trend_date=FISCAL_FIRST_OF_MONTH,"Medium",

If(IsBlank(Gantt_trend_date),"No Commit"

))))))))

[wdx223_Daniel]

it is ok in my PBI App?

[v-venuppu · Accepted solution]

Hi @ttocso ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @wdx223_Daniel for the prompt response.

FORMAT() is not supported in calculated columns. Instead, compare year and month directly with YEAR() and MONTH():

IF (
YEAR ( 'Table'[Gantt_trend_date] ) = YEAR ( 'Table'[FISCAL_FIRST_OF_MONTH] )
&& MONTH ( 'Table'[Gantt_trend_date] ) = MONTH ( 'Table'[FISCAL_FIRST_OF_MONTH] ),
"Medium"
)

Embed this inside your existing risk logic. This way the column correctly sets risk to Medium when both dates fall in the same year and month.

[ttocso]

Many thanks app, appreciate the support. I can confirm this works as expected. 

[ttocso]

Thx for the reply. It says FORMAT is an unknown or unsupported function? 

[wdx223_Daniel]

If(FORMAT(Gantt_trend_date,"YYMM")=FORMAT(FISCAL_FIRST_OF_MONTH,"YYMM"),"Medium",