Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Same week last year measure

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.

 

Need Help

3 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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