Calculate Age of Harvest with differing inputs

Hi- am getting confused on my time intelligence calcs :-|. Am trying to calculate the age of harvested fish based on month of harvest - “#Smolt Input” date; (baby fish) where the YC (year class), cohort and farm match.
So in the months where there are harvested fish I need to go back and find where there were smolt inputs match the YC, Cohort and farm and calculate age in months. There may be multiple input dates in which case we’d use an average of these.
Any quick suggestions on best Dax formula to use??
Have attached some dummy figures for one year class… thanks for any help! :slight_smile:YCSumm.pbix (201.8 KB)

Hi @kirsti.hansen, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Is this what you are looking for?

Age at Current DATE =
VAR TargetDate = TODAY()
VAR RowDate = STARTOFMONTH( Dates[Date] )
VAR MonthCount = DATEDIFF( RowDate, TargetDate, MONTH )

RETURN
IF(
MonthCount < 0,
“n/a”, MonthCount & " Months"
)

attached is your report, with this measure added (see tab 2 for how it is working)
Please also note that I changed the visual to point to the calendar for dates, not the month value on the YC18 table.
YCSumm.pbix (214.1 KB)

1 Like

Hi @kirsti.hansen, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Hi @Heather, thankyou so much for that, I will take a look and see how that works to calculate age back to “Smolt input” date. much appreciated.

1 Like

Hi again - this is great to calculate age to the current date, but need to pick up only until harvest date (date where harvest vol >0). I will keep going - this has helped thanks :slight_smile:

1 Like

Glad this gave you a starting place, hopefully it will help get you past the block.

Feel free to open a new post if you have additional questions :slight_smile: if I cannot help, I’m sure that you’ll hear from other members in this great forum! :slight_smile: