Forum Discussion

JulieGueho's avatar
JulieGueho
Regular Visitor
10 years ago
Solved

Modelling: how to correctly use time intelligence and Calculate?

Hi,   I'm doing classic sales report and I'm struggling to use Time Intelligence functions such as SAMEPERIODLASTYEAR, PREVIOUSMONTH, etc...   - I have a Dates table. - I have a Sales table with...
Need Help
  • JulieGueho's avatar
    JulieGueho
    10 years ago

    Thanks for your answer. I have the same error though.

     

    MdxScript(Model) (1, 71) Calculation error in measure 'Sales'[Sales Last Year]: Function 'DATEADD' only works with contiguous date selections.

    I think it doesn't work because I'm missing values for my previous year.

     

    The workaround I found was to use the filter YEAR(Today()) = Dates[Year] and YEAR(Today()) -1 = Dates[Year]

kcantor's avatar
kcantor
Community Champion
10 years ago

Honestly, I believe it is easier to use the DATEADD in this instance:
Sales Last Year = CALCULATE([Sales This Year] , DATEADD(Dates[Date]),-1,YEAR)

JulieGueho's avatar
JulieGueho
Regular Visitor
10 years ago

Thanks for your answer. I have the same error though.

 

MdxScript(Model) (1, 71) Calculation error in measure 'Sales'[Sales Last Year]: Function 'DATEADD' only works with contiguous date selections.

I think it doesn't work because I'm missing values for my previous year.

 

The workaround I found was to use the filter YEAR(Today()) = Dates[Year] and YEAR(Today()) -1 = Dates[Year]

  • nmorneau's avatar
    nmorneau
    New Member
    10 years ago

    Hello,

     

    Can you explain what you mean by this?

     

    The workaround I found was to use the filter YEAR(Today()) = Dates[Year] and YEAR(Today()) -1 = Dates[Year]

     

    I am having the same issue where I have some days that are missing transactions and getting a the same error with every time intelligence function I use.

     

    Thanks,

     

    Nick