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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |