Custom dates table Problem

[monsoonnut]

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.


[TABLE="width: 254"]
<tbody>[TR]
[TD]WEEK_IK[/TD]
[TD]WEEK[/TD]
[TD]YEAR[/TD]
[TD]YYYYWW[/TD]
[/TR]
[TR]
[TD]838[/TD]
[TD]4[/TD]
[TD]2015[/TD]
[TD]201504[/TD]
[/TR]
[TR]
[TD]839[/TD]
[TD]5[/TD]
[TD]2015[/TD]
[TD]201505[/TD]
[/TR]
[TR]
[TD]840[/TD]
[TD]6[/TD]
[TD]2015[/TD]
[TD]201506[/TD]
[/TR]
[TR]
[TD]841[/TD]
[TD]7[/TD]
[TD]2015[/TD]
[TD]201507[/TD]
[/TR]
[TR]
[TD]842[/TD]
[TD]8[/TD]
[TD]2015[/TD]
[TD]201508[/TD]
[/TR]
</tbody>[/TABLE]

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:


[TABLE="width: 335"]
<tbody>[TR]
[TD="colspan: 2"]Duplicate week[/TD]
[TD][/TD]
[TD="colspan: 2"]Week Removed[/TD]
[/TR]
[TR]
[TD]TY Week[/TD]
[TD]LY Week[/TD]
[TD][/TD]
[TD]TY Week[/TD]
[TD]LY Week[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[Eric Lacroix]

Hi,


not sure I understand your "variable length of timeframes used" as this make it not possible to know how to align the period.
What's not clear is how this can affect your LY but not TY?
Usually when referring to previous period logic, you need to know at least 1 section that remain the same (in your case could be week, but not sure why it's not the same) and you move across time by adjusting another component (year in this instance).
Alternatively , you could also add a column to SCMWEEK to include the previous period equivalent.

Again both suggestions might not be working if your not able to identify how to find the previous year matching period.

This would work in a normal situation:

=IF (
    HASONEVALUE ( SCMWEEK[WEEK_IK] ),
    CALCULATE (
        [Accuracy],
        FILTER (
            ALL ( SCMWEEK ),
            SCMWEEK[YEAR]
                = MAX ( SCMWEEK[YEAR] ) - 1
                && SCMWEEK[WEEK] <= MAX ( SCMWEEK[PREVIOUS YEAR WEEK] )
        )
    ),
    BLANK ()
)

Here's an excellent resource for Time patters: Time Patterns – DAX Patterns

Sharing your file would shed some light on your "variable length" and make it easier to understand your situation.

Eric

 

[monsoonnut]

Hi Eric

Thanks for the response.

After thinking about the problem I realised I am an idiot!

I was trying to create a dates table where I assigned last years weeks to this years weeks rather than assigning last years weeks to this years.

Once I cleared that up that error in my head (and model) all worked like a dream.

M

 

[monsoonnut]

Correction:

I was trying to create a dates table where I assigned this years weeks to last years weeks rather than assigning last years weeks to this years weeks