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 

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.

 

  • amitchandak's avatar
    amitchandak
    Super User
    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