Same week last year measure

[tmanoharan]

Hi,

I have created a measure for same week last year sales using the formula 

Sales SWLY = CALCULATE([Sales], FILTER(ALL('DimDate'), 'DimDate'[Year]=SELECTEDVALUE('DimDate'[Year])-1&& 'DimDate'[Week No]=SELECTEDVALUE('DimDate'[Week No]))).

When I try to bring the measure inside my table, I'm getting a error message stating that,

Error Message:
MdxScript(Model) (12, 83) Calculation error in measure 'vw_BUM_TM_Sitename'[Sales SWLY]: DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

[Anonymous]

Found a simple solution!

Using the DATEADD function I am able to subtract 364 days instead of the full 365 which ensure the calculation happens accurately. We are on a 52 week fiscal year which is 364 days or 28 days over 13 periods.

Thanks all for your contributions which inspired my solution.

Michael

[Cmcmahan]

I'm guessing for whatever reason, your Year is stored as text instead of as a number value.  The [Year]-1 could be causing a conversion and then the error.

Check to make sure that Year and WeekNo are all numerical format.

[tmanoharan]

Hi Cmcmahan,

You are absolutely right. The calendar table which I use has text format for year and week no. As soon as I changed that to whole numbers, I'm getting my results.

Thanks a lot.