Dynamic Rolling back 3 months from the Current month

Question

Hi All,

I have data set that I need to take the number based on the month.

EX: if the current month is June'23 then  I need a data from Octo'22 to Mar23 and Data should always start from the Oct month. if the new year starts (jan24) then Data should capture for the Octo23 month(Rolling back 3month)

Below is the data set

Data SetDateSaleSales Persons10-01-2022453881411-01-2022235253612-01-2022306499301-01-2023593131102-01-2023347407503-01-2023490741604-01-2023550927405-01-2023660401106-01-2023527152507-01-2023478025108-01-2023588302809-01-2023634018910-01-2023254866111-01-2023249195912-01-2023560974901-01-2024236907902-01-2024534793203-01-2024330665004-01-2024928516605-01-2024861987806-01-2024831998807-01-2024365393408-01-2024370645109-01-2024249195010-01-2024560976611-01-2024236907812-01-20245347988

Result that I need

Data SetResultDateSaleSales PersonsCurrent PeriodAvg Headcount10-01-20224538814  11-01-20222352536  12-01-20223064993  01-01-20235931311  02-01-20233474075  03-01-20234907416  04-01-20235509274  05-01-20236604011 AVERAGE(Oct'22 to Mar23)06-01-202352715255271540.8333333307-01-20234780251  08-01-20235883028  09-01-20236340189  10-01-20232548661  11-01-20232491959  12-01-20235609749 AVERAGE(Oct'23)01-01-20242369079236906102-01-20245347932  03-01-20243306650  04-01-20249285166  05-01-20248619878  06-01-20248319988  07-01-20243653934  08-01-20243706451  09-01-20242491950  10-01-20245609766  11-01-20242369078  12-01-20245347988

Thank you

geraldo · Answer

@shivanand612

an workflow example

I left months 11 and 12 for you to analyze in the multrow tool

Dynamicrolling.yxmd

shivanand612 · Answer

Hi geraldo,

below is the example for Feb, Mar and April

12-01-20235609749   01-01-202423690792369061AVERAGE(Oct'23)02-01-202453479325347960AVERAGE(Oct'23&Nov'23)03-01-202433066503306656.33333333AVERAGE(Oct'23, Nov'23 and Dec'23)04-01-202492851669285162AVERAGE(Oct'23, Nov'23,Dec'23&Jan'24)

geraldo · Answer

@shivanand612

these 2 months that you put in the sample can understand and apply the formula, but the other months feb, mar, apr .. how is the calculation?