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...
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

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