Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
narragirl85
Helper I
Helper I

Calculate and display moving 12 month Injury Frequency Rates on a dashboard

I need to show the 12-month moving LTIFR and TRIFR rates in a dashboard

I already have the values calculated and the targets, but I don't know how to show that in a kpi or gauge visual

Being able to display a slicer to select the month you want to view (if you're in November 23, you're looking at Dec 23 - Nov 23, if you've selected Aug 23, you're looking at Sep 22 - Aug 23 etc)

 

Data I use to calculate the LTIFR & TRIFR rate

MonthHoursLost Time InjuriesRestricted Duties InjuriesMedical Treatment InjuriesFirst Aid Treatment InjuriesNon-Treatment Injuries
Jan-20 00000
Feb-20 00000
Mar-20 00000
Apr-20 00000
May-20 00000
Jun-20 00000
Jul-20        9,28000010
Aug-20      14,24000000
Sep-20      25,74900120
Oct-20      30,79300000
Nov-20      36,38000010
Dec-20      33,44801100
Jan-21      38,60800000
Feb-21      36,68700000
Mar-21      37,83600000
Apr-21      51,80400000
May-21      37,32300000
Jun-21      42,36400000
Jul-21      41,45200000
Aug-21      41,82800000
Sep-21      46,42300000
Oct-21      44,03900010
Nov-21      58,93700020
Dec-21      44,59700022
Jan-22      49,03800010
Feb-22      38,76700001
Mar-22      60,83400010
Apr-22      49,46700020
May-22      58,67700112
Jun-22      58,86100010
Jul-22      55,73500010
Aug-22      60,58610010
Sep-22      60,97900010
Oct-22      63,52100130
Nov-22      76,62900130
Dec-22      66,26000110
Jan-23      67,27500010
Feb-23      77,88300000
Mar-23      74,04900000
Apr-23      72,46800100
May-23      77,28200130
Jun-23      70,06300000
Jul-23      62,45800000
Aug-23      58,04410000
Sep-23      57,88510000
Oct-23      67,03700000
Nov-23      68,94400000
Dec-23      68,30400000
Jan-24      68,47400000
Feb-24      68,57400000
Mar-24      67,79900000
Apr-24      67,27800000
May-24      66,84500000
Jun-24      65,97600000
Jul-24      65,63500000
Aug-24      65,90000000
Sep-24      66,55400000
Oct-24      67,27700000
Nov-24      67,29700000
Dec-24      67,15900000

 

Calculated values

Rolling Averages MonthRolling LTIFRLTIFR TargetRolling RDIFRRolling MTIFRRolling TRIFRTRIFR TargetRolling AIFR
Jan-21          -           5.31       10.61     15.92    37.14
Feb-21          -           4.44         8.88     13.32    31.09
Mar-21          -           3.80         7.60     11.41    26.61
Apr-21          -           3.18         6.35       9.53    22.23
May-21          -           2.84         5.68       8.52    19.88
Jun-21          -           2.53         5.07       7.60    17.74
Jul-21          -           2.34         4.69       7.03    14.06
Aug-21          -           2.20         4.40       6.60    13.21
Sep-21          -           2.11         2.11       4.21       6.32
Oct-21          -           2.05         2.05       4.10       8.19
Nov-21          -           1.96         1.96       3.92       9.79
Dec-210.00 0.000.000.00 13.41
Jan-220.00 0.000.000.00 15.03
Feb-220.00 0.000.000.00 16.84
Mar-220.00 0.000.000.00 17.94
Apr-220.00 0.000.000.00 21.62
May-220.00 0.001.731.73 27.76
Jun-220.00 0.001.691.69 28.67
Jul-220.00 0.001.651.65 29.64
Aug-221.60 0.001.603.20 31.95
Sep-221.56 0.001.563.12 32.79
Oct-221.52 0.003.034.55 36.36
Nov-221.48 0.004.435.90 38.37
Dec-221.43 0.005.727.15 34.32
Jan-231.395.980.005.576.977.6933.45
Feb-231.325.980.005.296.617.6930.39
Mar-231.305.980.005.206.497.6928.57
Apr-231.265.980.006.317.577.6926.48
May-231.235.980.006.167.397.6925.88
Jun-231.225.980.006.087.297.6924.31
Jul-231.215.980.006.037.237.6922.91
Aug-231.215.980.006.057.267.6921.77
Sep-232.435.980.006.078.507.6921.85
Oct-232.425.980.004.837.257.6916.92
Nov-232.445.980.003.666.107.6912.20
Dec-232.435.980.002.434.877.699.74
Jan-242.435.980.002.434.867.698.51
Feb-242.465.980.002.464.927.698.60
Mar-242.485.980.002.484.957.698.67
Apr-242.495.980.001.253.747.697.48
May-242.535.980.000.002.537.692.53
Jun-242.545.980.000.002.547.692.54
Jul-242.535.980.000.002.537.692.53
Aug-241.255.980.000.001.257.691.25
Sep-240.005.980.000.000.007.690.00
Oct-240.005.980.000.000.007.690.00
Nov-240.005.980.000.000.007.690.00
Dec-240.005.980.000.000.007.690.00
Jan-250.005.980.000.000.007.690.00
Feb-250.005.980.000.000.007.690.00
Mar-250.005.980.000.000.007.690.00
Apr-250.005.980.000.000.007.690.00
May-250.005.980.000.000.007.690.00
Jun-250.005.980.000.000.007.690.00
Jul-250.005.980.000.000.007.690.00
Aug-250.005.980.000.000.007.690.00
Sep-250.005.980.000.000.007.690.00
Oct-250.005.980.000.000.007.690.00
Nov-250.005.980.000.000.007.690.00
Dec-25#DIV/0!5.98#DIV/0!#DIV/0!#DIV/0!7.69#DIV/0!
4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @narragirl85,

It seems like you want to use slicer to achieve selector effect instead of filter effects.

For this scenario, you can create an unconnected table with date values and use this as source of filter.

Then you can write a measure formula to check the current table records and selection values to return tag and use this on 'visual level filter' to filter records within specific date ranges.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

I've used a work around for the meantime - I've just filtered the month column for the visual, but because powerbi is averaging the values I can't figure out another way to do it

 

I do have a separate date table that I can use - I just don't know what that formula would look like

 

narragirl85_0-1700708167522.png

 

lbendlin
Super User
Super User

Please explain what  LTIFR and TRIFR rates mean and how they are computed.

LTIFR - Lost Time Injury Frequency Rate

TRIFR - Total Recordable Injury Frequency Rate

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors