ISO week

[Veronika3]

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]

@Veronika3 , In weeknum try with 21 

Weeknum([Date], 21)

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

[Veronika3]

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]

@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]

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]

@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]

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 · Accepted solution]

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