topic Re: ISO week in DAX Commands and Tips

ISO week

Veronika3 — Wed, 19 Nov 2025 11:16:32 GMT

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

)

Re: ISO week

amitchandak — Wed, 19 Nov 2025 11:36:43 GMT

Veronika3 , In weeknum try with 21

Weeknum([Date], 21)

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

Re: ISO week

Veronika3 — Wed, 19 Nov 2025 12:07:28 GMT

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?

Re: ISO week

amitchandak — Wed, 19 Nov 2025 12:15:43 GMT

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 )

Re: ISO week

Veronika3 — Wed, 19 Nov 2025 12:29:33 GMT

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

Re: ISO week

amitchandak — Wed, 19 Nov 2025 12:40:20 GMT

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

Re: ISO week

Veronika3 — Wed, 19 Nov 2025 13:04:01 GMT

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.

Re: ISO week

amitchandak — Wed, 19 Nov 2025 15:01:47 GMT

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