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.
Hi everyone,
I want to compare the Sales of the last 12 months weekly and then for the context CalendarYearWeekISO.
Datamodel:
Time Measures:
FirstDate TW = FIRSTDATE( 'Calendar'[FirstDateOfWeek])
FirstDate LTM -1W =
CALCULATE([FirstDate TW];
FILTER(ALL('Calendar');
'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
'Calendar'[WeekOfYearISO] - 1= max ('Calendar'[WeekOfYearISO])
FirstDate LTM -1W TEST =
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNumber = CALCULATE(max('Calendar'[WeekOfYearISO] ); ALL('Calendar') )
RETURN
CALCULATE([FirstDate TW];
FILTER(ALL('Calendar');
IF( CurrentWeek = 52;
'Calendar'[WeekOfYearISO] = CurrentWeek -51 && 'Calendar'[Year] = CurrentYear;
IF( CurrentWeek = 53;
'Calendar'[WeekOfYearISO] = CurrentWeek -52 && 'Calendar'[Year] = CurrentYear;
'Calendar'[WeekOfYearISO] = CurrentWeek + 1 && 'Calendar'[Year] = CurrentYear -1))))
LastDate TW = LASTDATE ( 'Calendar'[LastDateOfWeek])
FirstDate PP LTM -1W =
CALCULATE([FirstDate TW];
FILTER(ALL('Calendar');
'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
'Calendar'[WeekOfYearISO] = max ('Calendar'[WeekOfYearISO])
)
)
LastDate PP LTM - 1W = DATEADD(LASTDATE('Calendar'[LastDateOfWeek]);-7;DAY)
As you can see in the following photo's, I have some null dates:
To compare weekly you have to know what the maximum weeknumber of the year prior was.
If the max. weeknumber lastyear = 52 than the calculations for the week 1 until 51 are as follows:
If the max. weeknumber lastyear = 53 than the calculations are:
I know, it also looks like mathematics to me but can someone help me with the correct Dax-formulas?
Thanks in advance,
Cor
PS: I don't have OneDrive or Dropbox and I don't know how to send a demo pbix-file.....
Solved! Go to Solution.
@Anonymous,
There are only 52 weeks in year 2003. Also check if the column [Year] is correct.
@Anonymous,
There are only 52 weeks in year 2003. Also check if the column [Year] is correct.
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |