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 Month Hours Lost Time Injuries Restricted Duties Injuries Medical Treatment Injuries First Aid Treatment Injuries Non-Treatment Injuries Jan-20 0 0 0 0 0 Feb-20 0 0 0 0 0 Mar-20 0 0 0 0 0 Apr-20 0 0 0 0 0 May-20 0 0 0 0 0 Jun-20 0 0 0 0 0 Jul-20 9,280 0 0 0 1 0 Aug-20 14,240 0 0 0 0 0 Sep-20 25,749 0 0 1 2 0 Oct-20 30,793 0 0 0 0 0 Nov-20 36,380 0 0 0 1 0 Dec-20 33,448 0 1 1 0 0 Jan-21 38,608 0 0 0 0 0 Feb-21 36,687 0 0 0 0 0 Mar-21 37,836 0 0 0 0 0 Apr-21 51,804 0 0 0 0 0 May-21 37,323 0 0 0 0 0 Jun-21 42,364 0 0 0 0 0 Jul-21 41,452 0 0 0 0 0 Aug-21 41,828 0 0 0 0 0 Sep-21 46,423 0 0 0 0 0 Oct-21 44,039 0 0 0 1 0 Nov-21 58,937 0 0 0 2 0 Dec-21 44,597 0 0 0 2 2 Jan-22 49,038 0 0 0 1 0 Feb-22 38,767 0 0 0 0 1 Mar-22 60,834 0 0 0 1 0 Apr-22 49,467 0 0 0 2 0 May-22 58,677 0 0 1 1 2 Jun-22 58,861 0 0 0 1 0 Jul-22 55,735 0 0 0 1 0 Aug-22 60,586 1 0 0 1 0 Sep-22 60,979 0 0 0 1 0 Oct-22 63,521 0 0 1 3 0 Nov-22 76,629 0 0 1 3 0 Dec-22 66,260 0 0 1 1 0 Jan-23 67,275 0 0 0 1 0 Feb-23 77,883 0 0 0 0 0 Mar-23 74,049 0 0 0 0 0 Apr-23 72,468 0 0 1 0 0 May-23 77,282 0 0 1 3 0 Jun-23 70,063 0 0 0 0 0 Jul-23 62,458 0 0 0 0 0 Aug-23 58,044 1 0 0 0 0 Sep-23 57,885 1 0 0 0 0 Oct-23 67,037 0 0 0 0 0 Nov-23 68,944 0 0 0 0 0 Dec-23 68,304 0 0 0 0 0 Jan-24 68,474 0 0 0 0 0 Feb-24 68,574 0 0 0 0 0 Mar-24 67,799 0 0 0 0 0 Apr-24 67,278 0 0 0 0 0 May-24 66,845 0 0 0 0 0 Jun-24 65,976 0 0 0 0 0 Jul-24 65,635 0 0 0 0 0 Aug-24 65,900 0 0 0 0 0 Sep-24 66,554 0 0 0 0 0 Oct-24 67,277 0 0 0 0 0 Nov-24 67,297 0 0 0 0 0 Dec-24 67,159 0 0 0 0 0 Calculated values Rolling Averages Month Rolling LTIFR LTIFR Target Rolling RDIFR Rolling MTIFR Rolling TRIFR TRIFR Target Rolling 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-21 0.00 0.00 0.00 0.00 13.41 Jan-22 0.00 0.00 0.00 0.00 15.03 Feb-22 0.00 0.00 0.00 0.00 16.84 Mar-22 0.00 0.00 0.00 0.00 17.94 Apr-22 0.00 0.00 0.00 0.00 21.62 May-22 0.00 0.00 1.73 1.73 27.76 Jun-22 0.00 0.00 1.69 1.69 28.67 Jul-22 0.00 0.00 1.65 1.65 29.64 Aug-22 1.60 0.00 1.60 3.20 31.95 Sep-22 1.56 0.00 1.56 3.12 32.79 Oct-22 1.52 0.00 3.03 4.55 36.36 Nov-22 1.48 0.00 4.43 5.90 38.37 Dec-22 1.43 0.00 5.72 7.15 34.32 Jan-23 1.39 5.98 0.00 5.57 6.97 7.69 33.45 Feb-23 1.32 5.98 0.00 5.29 6.61 7.69 30.39 Mar-23 1.30 5.98 0.00 5.20 6.49 7.69 28.57 Apr-23 1.26 5.98 0.00 6.31 7.57 7.69 26.48 May-23 1.23 5.98 0.00 6.16 7.39 7.69 25.88 Jun-23 1.22 5.98 0.00 6.08 7.29 7.69 24.31 Jul-23 1.21 5.98 0.00 6.03 7.23 7.69 22.91 Aug-23 1.21 5.98 0.00 6.05 7.26 7.69 21.77 Sep-23 2.43 5.98 0.00 6.07 8.50 7.69 21.85 Oct-23 2.42 5.98 0.00 4.83 7.25 7.69 16.92 Nov-23 2.44 5.98 0.00 3.66 6.10 7.69 12.20 Dec-23 2.43 5.98 0.00 2.43 4.87 7.69 9.74 Jan-24 2.43 5.98 0.00 2.43 4.86 7.69 8.51 Feb-24 2.46 5.98 0.00 2.46 4.92 7.69 8.60 Mar-24 2.48 5.98 0.00 2.48 4.95 7.69 8.67 Apr-24 2.49 5.98 0.00 1.25 3.74 7.69 7.48 May-24 2.53 5.98 0.00 0.00 2.53 7.69 2.53 Jun-24 2.54 5.98 0.00 0.00 2.54 7.69 2.54 Jul-24 2.53 5.98 0.00 0.00 2.53 7.69 2.53 Aug-24 1.25 5.98 0.00 0.00 1.25 7.69 1.25 Sep-24 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Oct-24 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Nov-24 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Dec-24 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Jan-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Feb-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Mar-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Apr-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 May-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Jun-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Jul-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Aug-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Sep-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Oct-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Nov-25 0.00 5.98 0.00 0.00 0.00 7.69 0.00 Dec-25 #DIV/0! 5.98 #DIV/0! #DIV/0! #DIV/0! 7.69 #DIV/0!Ability to create a measure based on selection for a visual OR use anotheslicer if none is selected?
Hi Everyone, I'm going to try my best to articulate what I'm looking for, but I have a bar chart where my x axis is fiscal years (last 10 years) and my Y axis is how much money was spent for each. I also have a gauge that shows total spent and paid visual as well. My issue I'm having is I want my gauge to default to this fiscal year we're in, but update if another fiscal year from the bar chart is selected. My problem i'm having is I dont know if there is a way to set it to default if nothing is selected. Right now with all the bar chart's FYs showing the gauge is totaling all of them up together which is inaccurate since our FYs act in silo's so I dont want mass total. So I want the guage to be this FY or adjust if a single year from the chart is selected and if nothing in the chart is selected then gauge goes back to the current FY. I'm hoping i'm making sense. Is this even possible? What I tried to do is add a FY slicer with today's FY as the first/default so that keeps the guage for this FY and turn off the bar chart to filter from that slicer, but then if i select a year from the bar chart to view the guage is blank. I am hoping there's a way to create a measure and reference the selected item from the chart otherwise use the FY slicer i added or something like that. Thank you!
Pace needed to hit budget
Hello! I'm trying to get the Target value to show pace needed to hit budget on the Gauge visual. Maxium is sales goal through current month & Value is the Net sales to date. I want it to update each day with the pace needed to hit budget based on todays date until end of current month. Here is one I used for a different slide Pace needed to hit budget = If('Date Table'[Work Day]="WorkingDay",CALCULATE(SUM('Production Budgets 2022'[Production Goal Per Day]),'Date Table'[Date].[Date]),BLANK()) -- the difference is I don't need an IF formula. Thank you!
Measure as Gauge visual filter
Hi, For our sales dep. I'm working on a report in which we compare the signed and expected (hot) deals against the target. The idea is to have slicers in place to select the status of a deal (for example, "signed, won") and to select if the deal is a hot deal or not. If the hot deal slicer is set to "yes", then the result set should show the deals with the selected statusses + all deals with hot deal = yes (regardless the status). So the slicers combined must use "OR" logic instead of the default "AND" logic. I'm trying to visualize the results (from table Query1) in a radial gauge visual. To make this work I created 2 unlinked tables with the statuses and the hot deal boolean and a measure "FilterMeasure" with the following DAX: FilterMeasure = CALCULATE((MAX(Query1[Status]) = SELECTEDVALUE(StatusTable[Status])) + (MAX(Query1[HotDeal]) = SELECTEDVALUE(HotDealTable[Value]))) The idea is to create slicers for the 2 unlinked tables and to add the measure as a visual filter with expression > 0. The problem is that this approach isn't working for the Gauge visual. I can add the measure as a visual filter to the gauge, but I'm unable to set the parameters: Using the measure as a visual filter on a table is working: For sure the DAX command isn't correct, but I don't know how to write the DAX for the measure so that it can be used as a visual filter on a gauge visual. Or maybe the a complete different approach to achieve the goal to append records to the result set without considering the selected slicer value. Hope someone can help me out.
