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 be. Could you please advise?

 

NC First PY C ISO = IF(
    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()
)
  • 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 

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    9 months ago

    Veronika3 , In weeknum try with 21 

     

    Weeknum([Date], 21)

    Now, using year and week, we can create year weeknum and calculate rest 

    • 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 )