Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to split Monthwise data to Fiscal Week

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

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    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

1 Reply

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    3 years ago

    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