monsoonnut
New Member
- Joined
- Jul 1, 2016
- Messages
- 13
Hi
I am working on a forecast accuracy report that runs weekly - I have a custom dates table ('SCMWEEK') that contains weeks with WEEK_IK being the Key.
<tbody>
</tbody>
This table is linked to my data table via the YYYWW column and I have created calculations for LW using the GFITW and all is working:
Accuracy Wk-1:=IF(HASONEVALUE(SCMWEEK[WEEK_IK]),CALCULATE([Accuracy],
ALL('SCMWEEK'),FILTER(ALL('SCMWEEK'),SCMWEEK[WEEK_IK]=VALUES(SCMWEEK[WEEK_IK])-1)),BLANK())
However, when I want to compare TY vs. LY I am running into problems due to the variable length of timeframes used, meaning I cannot just amend the -1 in the above formula to -52.
The model used means that when comparing with LY an extra week may need to be added or a week removed to keep the Yr on Yr comparisons aligned:
<tbody>
</tbody>
I assume I will need another look-up table but I am not sure what format this would need to take or which DAX formulas are required to return the correct data.
Any help appreciated!
I am working on a forecast accuracy report that runs weekly - I have a custom dates table ('SCMWEEK') that contains weeks with WEEK_IK being the Key.
WEEK_IK | WEEK | YEAR | YYYYWW |
838 | 4 | 2015 | 201504 |
839 | 5 | 2015 | 201505 |
840 | 6 | 2015 | 201506 |
841 | 7 | 2015 | 201507 |
842 | 8 | 2015 | 201508 |
<tbody>
</tbody>
This table is linked to my data table via the YYYWW column and I have created calculations for LW using the GFITW and all is working:
Accuracy Wk-1:=IF(HASONEVALUE(SCMWEEK[WEEK_IK]),CALCULATE([Accuracy],
ALL('SCMWEEK'),FILTER(ALL('SCMWEEK'),SCMWEEK[WEEK_IK]=VALUES(SCMWEEK[WEEK_IK])-1)),BLANK())
However, when I want to compare TY vs. LY I am running into problems due to the variable length of timeframes used, meaning I cannot just amend the -1 in the above formula to -52.
The model used means that when comparing with LY an extra week may need to be added or a week removed to keep the Yr on Yr comparisons aligned:
Duplicate week | Week Removed | |||
TY Week | LY Week | TY Week | LY Week | |
1 | 1 | 1 | 1 | |
2 | 2 | 2 | 2 | |
3 | 3 | 3 | 3 | |
4 | 4 | 4 | 4 | |
5 | 4 | 5 | 6 | |
6 | 5 | 6 | 7 | |
7 | 6 | 7 | 8 | |
8 | 7 | 8 | 9 | |
9 | 8 | 9 | 10 | |
10 | 9 | 10 | 11 |
<tbody>
</tbody>
I assume I will need another look-up table but I am not sure what format this would need to take or which DAX formulas are required to return the correct data.
Any help appreciated!