Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ChrisGec
Frequent Visitor

DAX Calender Last Year Iso Week

Hello everyone,

 

I've got a DAX Calendar Table:

 

 

Kalendar = CALENDAR (DATE(2013,1,1), DATE(2025,12,31))

 

 

 Within this table I've got these DAX Columns (since datediff can't work with IsoWeeks I have to build my own workaround):

 

 

IsoWeekNumber = WEEKNUM([Date],21)

Year = YEAR([Date])

WeekofYear = WEEKNUM([Date],2)

IsoYear = IF([IsoWeekNumber]<5 && [WeekofYear] > 50,
     [Year]+1,
     IF([IsoWeekNumber]>50 && [WeekofYear]<5, 
          [Year]-1, 
          [Year]))
YearIsoWeekofYear = CONCATENATE([IsoYear],IF([IsoWeekNumber]<10,CONCATENATE("0",[IsoWeekNumber]),[IsoWeekNumber]))

Running Week Years = CALCULATE(DISTINCTCOUNT(Kalendar[YearIsoWeekofYear]),ALL(Kalendar),Kalendar[YearIsoWeekofYear] <= EARLIER(Kalendar[YearIsoWeekofYear]))

 

 

 
And I've got these two Measures:

 

 

Current Running Week Years = CALCULATE(AVERAGE(Kalendar[Running Week Years]), Kalendar[Date] = TODAY())

LastYear Running Week Years = 
VAR currentw = [Current Running Week Years]
VAR lywn = CALCULATE(AVERAGE(Kalendar[YearIsoWeekofYear]), Kalendar[Running Week Years] = currentw)-100
RETURN
// lywn
CALCULATE(AVERAGE(Kalendar[Running Week Years]), FILTER(ALL(Kalendar[YearIsoWeekofYear]), Kalendar[YearIsoWeekofYear] = lywn))

 

My problem is the Measure for the last year.
If I change it, to show the variable lywn it works perfectly fine and gives me for my current week (202025) 201925. When i try to use this in the filterexpression it doesn't give me any result and I have no idea why this doesn't work.
My goal would be a column "
IsInPrevYearLastWeek" which should look like 

 

IsInPrevYearLastWeek = IF([LastYear Running Week Years]-Kalendar[Running Week Years] = 1,1,0)

 

to filter some Measures.

I would be really happy if anyone has a solution for this.

2 REPLIES 2
lbendlin
Super User
Super User

I hope this is a typo 

IsoWeekNumber = WEEKNUM([Date],21)

 

So you want to identify all the dates in the previous year that are in the same ISO week as this year?

 

Where is the Typo? The 21 as Parameter is undocumented but works as in Excel. It gives me the Weeknumber to a date with Iso rules applied to it.
Yeah the Measure should always give me the the same Weeknumber we have this year but in last year. For this week, which is 202025, ist should provide 201925.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors