Forum Discussion

katto16's avatar
katto16
Helper I
2 years ago
Solved

Current week DAX

Trying to get the current week number dynamically instead of 14 but haven't been successful.

 

SWITCH(
    SELECTEDVALUE('Calendar'[FY Week]), 14,
    CALCULATE(SUM(table[ORDERs]), table[STATUS]="Closed")
)

 

 My FY week has already been defined as a calculated column. It follows my FY which is from Sep to Oct. 

 

FY Week = 
VAR Weekno = WEEKNUM('Calendar'[Date],21)
VAR FYWeekNo = WEEKNUM(DATE(YEAR('Calendar'[Date]),10,01),2)
RETURN
    IF(
        Weekno >= FYWeekNo,
        Weekno - (FYWeekNo),
        52+Weekno-(FYWeekNo)
    )

 

  Any help is appreciated. Thanks in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi katto16 ,

    If I understand correctly, the issue is that you want to calculate the week number dynamically. Please try the following methods and check if they can solve your problem:

    1.You need to create a measure that calculates the current week number.

    Current FY Week Number = 
VAR CurrentDate = TODAY()
VAR Weekno = WEEKNUM(CurrentDate, 21)
VAR FYStart = DATE(YEAR(CurrentDate), 10, 1)
VAR FYWeekNo = WEEKNUM(FYStart, 2)
RETURN
    IF(
        Weekno >= FYWeekNo,
        Weekno - FYWeekNo + 1,
        52 + Weekno - FYWeekNo + 1
    )

    2.Using the measure within the calculation.

    FY Week = 
CALCULATE(
    SUM(table[ORDERS]),
    table[STATUS] = "Closed",
    'Calendar'[FY Week] = [Current FY Week Number]
)

    If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format or screenshot to make a deep troubleshooting? It would be helpful to find out the solution.

     

    Looking forward to your reply.

    Best Regards,

    Wisdom Wu

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi katto16 ,

    If I understand correctly, the issue is that you want to calculate the week number dynamically. Please try the following methods and check if they can solve your problem:

    1.You need to create a measure that calculates the current week number.

    Current FY Week Number = 
VAR CurrentDate = TODAY()
VAR Weekno = WEEKNUM(CurrentDate, 21)
VAR FYStart = DATE(YEAR(CurrentDate), 10, 1)
VAR FYWeekNo = WEEKNUM(FYStart, 2)
RETURN
    IF(
        Weekno >= FYWeekNo,
        Weekno - FYWeekNo + 1,
        52 + Weekno - FYWeekNo + 1
    )

    2.Using the measure within the calculation.

    FY Week = 
CALCULATE(
    SUM(table[ORDERS]),
    table[STATUS] = "Closed",
    'Calendar'[FY Week] = [Current FY Week Number]
)

    If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format or screenshot to make a deep troubleshooting? It would be helpful to find out the solution.

     

    Looking forward to your reply.

    Best Regards,

    Wisdom Wu

     

     

     

    • katto16's avatar
      katto16
      Helper I
      2 years ago

      Hi Wis. I did some minor changes like CALCULATE(SUM(FILTER))) and took out +1 in week calculation. Seems to be working now. Thanks for the help!