Adding Last data point label to a 12mth Calculation

[Anonymous]

Hi,

I have managed to setup a 12mth rolling measure that calulates a frequency rate of injuries against hours:

• '12mth TRIFR' = '12mth injury count' * 1000000 / '12mth hours'
  • '12mth injury count' = CALCULATE ( SUM ( MonthEnd[TRI] ), DATESINPERIOD (MonthEnd[Date], VALUES ( MonthEnd[Date] ), -12, MONTH ) )
  • '12mth hours' = CALCULATE ( SUM ( MonthEnd[Hours] ), DATESINPERIOD (MonthEnd[Date], VALUES ( MonthEnd[Date] ), -12, MONTH ) )

This all plots as i would expect with the correct values.

I was able to find guides on how to show the last data point but everything i try doesnt give me the correct answer. What i get is the the most recent TRFIR but its based of 1 month of data rather than a 12mth (which i am sure if based on the filter of the current month). So my approach appears to stifle my result as well as giving me a single data label.

Is there anyway to get the 12mth calculation to work as well as only show the last data point?

Thanks,

Darren

[lbendlin]

@Anonymous I am surprised that your VALUES() part doesn't throw an error. The second parameter of DATESINPERIOD expects a single start date. Maybe VALUES() returns a single row table.

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) and show the expected outcome.