Forum Discussion

StephenGW's avatar
StephenGW
Helper II
4 years ago
Solved

Fiscal Week Reset

All,   I found this code for the advanced editor on here somewhere but I struggle to pick it apart to the level I need.   let // Enter the date in Advanced Editor for the first day of the earlie...
Need Help
  • Icey's avatar
    Icey
    4 years ago

    Hi StephenGW ,

     

    Please check if this could meet your requirements:

    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,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
4 years ago

StephenGW , In DAX if you want

 

 

Week No = var _1= QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date], "1/31"),'Date'[Date],DAY),7)+1 return _1

 

or


Week No =
Var _st = if(month([Date]) <2 , date(year([Date])-1,2,1) , date(year([Date]),2,1) )
var _1= QUOTIENT(DATEDIFF(_st,'Date'[Date],DAY),7)+1
return _1

 

 

amitchandak's avatar
amitchandak
Super User
4 years ago

StephenGW , Power Query

 

Not tested

Number.IntegerDivide(Duration.Days([Date]-(if Date.Month([Date]) <2 then #date(Date.Year([Date])-1,2,1) else #date(Date.Year([Date]),2,1) )),7)+1