How to calculate Number of month is a year

[mdaamirkhan]

Hi All

calculate Number of month is a year as per my below it return 11 it should be 10 screenshot below. what will be dax query

Example1 for contract 10A040-06. End date is 10/31/2021 therefore calculation should be 10 . Currently it showing is 11

Example2 for contract 10101204. End date is 2/28/2022 therefore calculation should be  12. Currently it showing is 11

Number of service month =

VAR _start = STARTOFYEAR(Input_Contract_Report[End Date] )

var _yr = YEAR(NOW())

VAR _end = DATE(_yr,12,31)

RETURN

ABS(DATEDIFF( _start, _end, MONTH ))

[AlexisOlson]

It's not clear to me what your logic is intended to do.

Why should 10/31/2021 give 10 and 2/28/2022 give 12?

[mdaamirkhan]

because it not count whole year as per current month.
If I have in a year 2021 fronm Jan to dec then as per date 10/31/2021 it should count as 10 monthsin a year

[AlexisOlson]

OK. 10 months for 10/31 is easy to understand but you did not explain why 2/28 should give 12. Why not 2 or 14  or something else?

[mdaamirkhan]

logic will be this

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

[v-janeyg-msft]

Hi, @mdaamirkhan 

You can modify your measure like this:

Number of service month =
VAR _start =
    STARTOFYEAR ( 'Table'[date] )
VAR _yr =
    YEAR ( NOW () )
VAR _end =
    DATE ( _yr, 12, 31 )
VAR a =
    DATEDIFF ( _start, _end, MONTH )
RETURN
    IF ( a < 12 && a > 0, MONTH ( _start ), IF ( a < 0, 12 ) )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[mdaamirkhan]

A per your query For Feb 2022 it will be 2 not 12. If 2023 then it will be 12

[v-janeyg-msft · Accepted solution]

Hi, @mdaamirkhan 

I wrote the last formula based on the logic you said.

And you said 'A per your query For Feb 2022 it will be 2 not 12. If 2023 then it will be 12' ,So I modify the measure.

Like this:

Number of service month =
VAR _start =
    STARTOFYEAR ( 'Table'[date] )
VAR _yr =
    YEAR ( NOW () )
VAR _end =
    DATE ( _yr, 12, 31 )
VAR b =
    DATEDIFF ( _end, _start, MONTH )
VAR year =
    YEAR ( MAX ( 'Table'[date] ) )
RETURN
    IF ( year = _yr, MONTH ( _start ), IF ( year > _yr, b ) )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[mdaamirkhan]

When I use this query [Number of service month_2]  its not showing the value for Current year +1 , Current year +2 and Current year +3.
Can you please help me on this what is the issue in the below dax or in [Number of service month that you have provided .

Estimated revenue Current year +1 =

VAR thisYear = YEAR ( now()) +1

VAR val =

CALCULATE (

[Number of service month_2], -- COUNTA ( Input_Contract_Report[months] ),

FILTER ( Input_Contract_Report, Input_Contract_Report[Active] = "Yes" ),

FILTER (ALL(

Input_Contract_Report),

Input_Contract_Report[End Date].[Year] = thisYear

),

ALLEXCEPT('Input_Contract_Report (2)','Input_Contract_Report (2)'[Column])

)

RETURN

ABS( val * [Average revenue])

Estimated revenue Current year +2 =

VAR thisYear = YEAR ( now()) +1

VAR val =

CALCULATE (

[Number of service month_2], -- COUNTA ( Input_Contract_Report[months] ),

FILTER ( Input_Contract_Report, Input_Contract_Report[Active] = "Yes" ),

FILTER (ALL(

Input_Contract_Report),

Input_Contract_Report[End Date].[Year] = thisYear

),

ALLEXCEPT('Input_Contract_Report (2)','Input_Contract_Report (2)'[Column])

)

RETURN

ABS( val * [Average revenue])