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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors