Forum Discussion

Veronika3's avatar
Veronika3
Regular Visitor
9 months ago
Solved

ISO week

Hi again,  I'm having an issue with the metric I found here. It's the ISO week and the MOD formula. After using the combination, the numbers aren't displaying and I'm not sure where the error could ...
  • amitchandak's avatar
    amitchandak
    9 months ago

    Veronika3 , You can use weekday in filter like WEEKDAY(today() )<7 or WEEKDAY([Date])<7
    1 means sunday start , 2 means Monday start 

Veronika3's avatar
Veronika3
Regular Visitor
9 months ago

Thank you. I use this:

NC First PY C ISO = IF(
    VAR weeknum = WEEKNUM(TODAY(), 21)
RETURN
    MOD(YEAR(TODAY() - 1), 7) = 0,
    CALCULATE(
        [NC First Ever],
        DATEADD(dimDate[DateDay], -1, YEAR),
        dimDate[DateDay] <= DATE(YEAR(TODAY() - 1) - 1, MONTH(TODAY() - 1), DAY(TODAY() - 1))
    ),
    BLANK()
)
 
But I thing, is not correct. Do you?
amitchandak's avatar
amitchandak
Super User
9 months ago

Veronika3 , if you are only looking for week year behind 

CALCULATE(
[NC First Ever],
DATEADD(dimDate[DateDay], -364, DAY))

 

Please let me know what exact formula you are looking for (Logic of calculation )

  • Veronika3's avatar
    Veronika3
    Regular Visitor
    9 months ago

    The logic is that if I want to compare the third weekend in 2024 vs. 2025, it should be an ISO weekend, so regardless of the date.
    For example, comparing the number of new customers (NC First Ever). By clicking the filter on weekend 23, the graph should show last year's weekend 23 and this year's weekend 23 regardless of the date.
    I insert NC First Ever PY ISO into the formula Previous ISO = if(max('First x First ever'[ID]) = 1 ,[NC First Ever PY ISO],[NC First PY C ISO]).
    When the Current and Previous ISO metrics are next to each other, it should always be the same weekend (regardless of the date).

    • amitchandak's avatar
      amitchandak
      Super User
      9 months ago

      Veronika3 , Create these column in you date table 

      ISO Week = Weeknum([Date], 21)
      ISO Year = if(Month([Date]) =1 &&Weeknum([Date], 21) >45, Year([Date]) -1, Year([Date]) 

      ISO Year Week = [ISO Year] *100 + [ISO Week]

      This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[ISO Year]=max('Date'[ISO Year]) && 'Date'[ISO Week] = Max('Date'[ISO Week]) ))
      Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[ISO Year]=max('Date'[ISO Year])-1 && 'Date'[ISO Week] = Max('Date'[ISO Week])))

      You can use weekday column for weekend 

      • Veronika3's avatar
        Veronika3
        Regular Visitor
        9 months ago

        I basically have ISO days guaranteed with this formula:
        NC First Ever PY ISO =
        calculate([NC First Ever],dateadd(dimDate[DateDay],-1,YEAR),dimDate[DateDay] <= DATE(YEAR(TODAY()-1)-1,MONTH(Today()-1),DAY(TODAY()-1)))

        and I just need to get the MOD formula to the above to ensure a distinct function where the division by 7 will take place, if the weekend is not divisible by 7 it will return "blank".

        Unfortunately I am not allowed to create a new column.