How to split Monthwise data to Fiscal Week

[Anonymous]

Hi

I have a budget table, yearmonth and material wise.

I would like to split the budget data based other sales data I have which is weekwise to dispaly both Sales and Budget weekwise in a single report.

PS: We have a Calander Table with year, month, week and FY columns.

Can anyone please let me know how to split the yearmonth data based on the weeks refering to the Calander table.

Thanks
Mahad

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Please check if  the following is helpful for you,or share your sample data and expect output:  

FY = 
IF (
    [Date] >= DATE ( [Year], 1, 3 )
        && [Date]
            <= DATE ( [Year] + 1, 1, 2 ),
    "FY" & RIGHT ( [Year], 2 ),
    "FY"
        & RIGHT ( [Year] - 1, 2 )
)

FW = 
RANKX (
    FILTER ( FiscalWeek, FiscalWeek[FY] = EARLIER ( FiscalWeek[FY] ) ),
    [FW_Index],
    ,
    ASC,
    DENSE
)

Best Regards

Lucien