Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    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?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.