Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Previous week calculation

Hi everyone,

 

Iam calculating the total number of previous week to have change week over week with code below, but in the result it shows same number for every weeks.

PreviousWeekCount =
VAR CurrentWeekOffset = MAX('CALENDAR'[WeekNumber])
RETURN
CALCULATE(
    COUNT('E'[Order Number]),
    FILTER(
        ALL('Calendar'),
        'CALENDAR'[WeekNumber] = CurrentWeekOffset - 1
    )
)
 
Please help.
Thank you guys in advance
Need Help

4 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    you need to add the following column to the calendar table and then create this measure
    numweek = DATEDIFF(min('Calendar'[Date]),'Calendar'[Date],WEEK)


    PreviousWeekCount =
    VAR PreviosWeekOffset = MAX('CALENDAR'[numweek]) -1
    RETURN
    CALCULATE(
    COUNT('E'[Order Number]),
    FILTER(
    ALL('Calendar'),
    'CALENDAR'[numweek] = Previos
    )
    )

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    What is your definition of week?  How is this supposed to work across years?

     

    Safest approach is to subtract 7 days.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi guys, Ahmedx lbendlin 

     

    I have table calendar here, I still can calculate change in Weeknumber  with code above (i guess because it is number), but i wanna display in Weekdisplay (text), so if i apply for Weekdisplay with same code, just change weeknumber = weekdisplay it did not work.

     

    Please help, thank you guys.

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      week numbers won't work reliably across year boundaries. Safest approach is to subtract 7 days or to divide the day number by 7.