Rolling 3 month Running Total value and get hit rate with Dax or ...?

iamJL — Tue, 21 Jan 2025 03:21:48 GMT

I need to get hit rate, I wrote a measure but it seems like too much data to show in the BI matrix table..

all my data is about 30,000 rows, data structure is like excel picture

maybe someone can solve it with better solution...?

step 1: rolling total qty evey 3 months GROUP BY SALES AND PARTNO IN TABLE A AND B
step 2: new_ship_qty/new_forcast_qty*100% ---> get hit rate.

TABLE A

---->

NEW TABLE A

TABLE B

---->

NEW TABLE B

Re: Rolling 3 month Running Total value and get hit rate with Dax or ...?

bhanu_gautam — Tue, 21 Jan 2025 06:37:15 GMT

iamJL First, create measures to calculate the rolling 3-month total for both forecast and ship quantities.

RollingForecastQTY =
CALCULATE(
SUM('TableA'[Forcast_QTY]),
DATESINPERIOD('TableA'[YYMMDD], LASTDATE('TableA'[YYMMDD]), -3, MONTH)
)

RollingShipQTY =
CALCULATE(
SUM('TableB'[Ship_QTY]),
DATESINPERIOD('TableB'[YYMMDD], LASTDATE('TableB'[YYMMDD]), -3, MONTH)
)

Next, create a measure to calculate the hit rate based on the rolling totals.

HitRate =
DIVIDE(
[RollingShipQTY],
[RollingForecastQTY],
0
) * 100

Add the HitRate measure to your BI matrix table to visualize the hit rate over time.