revenue this year and revenue this year+1

[mdaamirkhan]

Hi All

I need help on the folloing logic what will be dax

revenue this year = if("contract end date"- 01/01/this year)<12 then ("contract end date"- 01/01/this year) else 12

revenue this year+1 = if("contract end date"- 01/01/this year+1)<12 then ("contract end date"- 01/01/this year+1) else 12

[Ashish_Mathur]

Hi,

Your question is not clear.  Share some data and show the expected result.

[DataZoe · Accepted solution]

@mdaamirkhan I am not 100% sure what the expected outcome you are looking for here is, but the logic you have outlined in a measure could look like this:

Revenue This Year =
VAR MaxEndDate = MAX ( Table[Contract End Date] )
VAR ThisYear = DATE ( YEAR ( TODAY () ), 1, 1 )
VAR Diff = MaxEndDate - ThisYear
RETURN
IF ( Diff < 12, Diff, 12 )

Revenue This Year +1 =
VAR MaxEndDate = MAX ( Table[Contract End Date] )
VAR ThisYear = DATE ( YEAR ( TODAY () ), 1, 1 )
VAR Diff = MaxEndDate - (ThisYear+1)
RETURN
IF ( Diff < 12, Diff, 12 )

[mdaamirkhan]

Its not working in 2022 i have only Jan so it should return number of month 1

Revenue This Year +1 =
VAR MaxEndDate = MAX ( Table[Contract End Date] )
VAR ThisYear = DATE ( YEAR ( TODAY () ), 1, 1 )
VAR Diff = MaxEndDate - (ThisYear+1)
RETURN
IF ( Diff < 12, Diff, 12 )

[DataZoe]

@mdaamirkhan 

Try this:

Revenue This Year +1 =
VAR MaxEndDate = MAX ( Table[Contract End Date] )
VAR ThisYear = DATE ( YEAR ( TODAY () )+1, 1, 1 )
VAR Diff = (DATEDIFF(ThisYear,MaxEndDate,MONTH)) +1
RETURN
IF ( Diff < 12, Diff, 12 )

[v-yetao1-msft]

Hi @mdaamirkhan ,

I’m not sure exactly what you want , but it could be like this :

Revenue This Year =

VAR MaxEndDate = MAX ( 'Contract End Date'[Contracts end data])

VAR ThisYear = DATE ( YEAR ( TODAY () ), 1, 1 )

VAR Diff = DATEDIFF(ThisYear,MaxEndDate,MONTH)

RETURN IF ( Diff < 12, Diff, 12 )

Revenue This Year +1 =

VAR MaxEndDate = MAX ('Contract End Date'[Contracts end data] )

VAR ThisYear = DATE ( YEAR ( TODAY () )+1, 1, 1 )

VAR Diff = (DATEDIFF(ThisYear,MaxEndDate,MONTH)) +1

RETURN IF ( Diff < 12, Diff, 12 )

Through two variables to get var diff ,and then output the result by judging the condition .

The effect is as shown(The specified time is 2021/01/01):

Best Regards

Ailsa Tao

[mdaamirkhan]

Not working coz it return 1 only 

[mdaamirkhan]

As per your query  it return 12 which wrong it should show 5
Revenue This Year =
VAR MaxEndDate = MAX ( Table[Contract End Date] )
VAR ThisYear = DATE ( YEAR ( TODAY () ), 1, 1 )
VAR Diff = MaxEndDate - ThisYear
RETURN
IF ( Diff < 12, Diff, 12 )

[DataZoe]

@mdaamirkhan The logic you provided assumes the difference is in months, not days? If that is what you are looking for, then try this change:

Revenue This Year =
VAR MaxEndDate = MAX ( Table[Contract End Date] )
VAR ThisYear = DATE ( YEAR ( TODAY () ), 1, 1 )

VAR Diff = DATEDIFF(ThisYear,MaxEndDate,MONTH)

RETURN
IF ( Diff < 12, Diff, 12 )