Rolling 12 Months Calculation

princye

Trying to get the below into DOMO (below is done in PBI)

Monthly - 01/12/2024 - 31/12/2024 (1 Month) - Works for each month using the calculation - COUNT(DISTINCT Incident Number)

Rolling - 01/01/2024 - 31/12/2024 (12 Months) - Need help

FYTD - 01/04/2024 - 31/12/2024 (Apr 2024 - Dec 2024) - Need help

ColemenWilson

You can use the following for SSI Rolling:

(

SUM(SSI Monthly) +

LAG(SUM(SSI Monthly), 11) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 10) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 9) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 8) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 7) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 6) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 5) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 4) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 3) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 2) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 1) OVER (ORDER BY Year-Month))

FYTD:

(

SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 11) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 10) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 9) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 8) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 7) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 6) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 5) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 4) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 3) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 2) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 1) OVER (ORDER BY Year-Month))

Results (I don't have the rest of your data to go farther back, but the numbers are matching with what I do have: