How to Show the Current month Value which is Jul-24 value for Future Months ?

Steelbird

Below is the Existing Report & I am trying to add a GSP calculated column out of it.

Avg = Gross Sales / Gross Sales Units

3 Months Avg = Sum of amounts apr-24 + may-24 + Jun-24 / Sum of Units apr-24 + may -24 + Jun-24

6 Months Avg = Sum of amounts Jan-24 + Feb -24 + Mar -24 +apr-24 + may-24 + Jun-24 / Sum of Units Jan-24 + Feb -24 + Mar -24 + apr-24 + may -24 + Jun-24

GSP: CASE WHEN 3 Months Avg = 0 THEN 6 Months Avg else 3 Months Avg end

For future months also we need to show the Current month Value which is Jul-24 value.

I need this logic to built in OBI Answers or rpd layer

Currently for 3 Month & 6 Months - I am creating columns in 2 ways using Ago function & Period Rolling

Using Ago Function:

Formual for 3 Months Ago - ( Amount 1 month Ago + Amount 2 month Ago + Amount 3 month Ago / Quantity 1 month Ago + Quantity 2 month Ago + Quantity 3 month Ago)

Formual for 6 Months Ago - ( Amount 1 month Ago + Amount 2 month Ago + Amount 3 month Ago + Amount 4 month Ago + Amount 5 month Ago+ Amount 6 month Ago/ Quantity 1 month Ago + Quantity 2 month Ago + Quantity 3 month Ago + Quantity 4 month Ago + Quantity 5 month Ago + Quantity 6 month Ago)

Using Period Rolling Function:

periodrolling(SLA Amount,-3,-1)/periodrolling(Quantity,-3,-1)

periodrolling(SLA Amount,-6,-1)/periodrolling(Quantity,-6,-1)