Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Adding Last data point label to a 12mth Calculation

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

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    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.