Need to use a measure to post a number, then a fixed value

[Anonymous]

Hello team.

I need help with a measure that I already created. 

Average time = 12 -MONTH(FORMAT(AVERAGE(Capex[OT Date]), "MM/DD/yyyy"))

This information is in one table only.

So I am taking a date subtracting 12 and putting the number in a year column, this works, the problem is for the rest of them I need to put a 12 or fixed number.

 So basically in 2015 its a 3, and for the rest a 12.

for 2016 a 2 and rest a 12. and so on.

[v-xicai]

Hi @Anonymous ,

There will be an error like "can't convert text type to Date type" using your original DAX formula "Average time = 12 -MONTH(FORMAT(AVERAGE(Capex[OT Date]), "MM/DD/yyyy")) ". You can create measure like DAX below, then display the result in Matrix table.

Average time = 12 -MONTH(AVERAGE(Capex[OT Date]))

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYh8Yf4UodBCs6D1ImKZ2OIBk3Gi3vHLz3wGDmWG2hKp7g?e=ouQ1Jg

Best Regards,

Amy

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

[Anonymous]

Hello, My dax formula does not give an error, I just need to put the first month in the subtraction like in my measure, and from there on put a 12, but for months before a 0 for for 2016, on the 2015 column it will be a 0,

Thanks for the help. I think i need to clarify: .

• For example, if the average date in the OT Actual Date field is 7/1/15, the difference between that date and the end of the year is 5 months. Therefore 5 will be populated in the field for 2015
  • All subsequent years will be populated with 12 except for the current year which will reflect the number of months already passed, before this year it will be a 0

[Anonymous · Accepted solution]

developed the solution.

Average Holding Period = IF(FIRSTNONBLANK(Capex[Fully E. N. Leases & A. Year],TRUE()) = YEAR(TODAY()), MONTH(TODAY()),  IF(LASTNONBLANK(Capex[Fully E. N. Leases & A. Year], TRUE())  = LASTNONBLANK(Capex[Built Date],TRUE()) ,     12 - (month(average(Capex[OTHVR Actual Date])))   ,    12  ))