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 sales by day for the two past years.

- The table are related by the datekey column

 

Problem 1: Year over year

I created two measures

Sales This Year = TOTALYTD(SUM(Revenue), Dates[Date])

Sales Last Year = CALCULATE([Sales This Year] , SAMEPERIODLASTYEAR(Dates[Date]))

 

-> I get the right values but if I try to plot the two measures by month on a bar chart, I get the non contiguous date error. I guess that's because some month don't have any sales last year.

 

Problem 2: Month over month

I created two measures

Sales July 2015 = CALCULATE(SUM(Revenue), DATESBETWEEN(Dates[Date], DATE(2015,7,1), DATE(2015,7,31)))

Sales Previous Month = CALCULATE([Sales July 2015], PREVIOUSMONTH(Dates[Date]))

 

-> I get Sales July 2015 = Sales Previous Month

If I do: Sales Previous Month = CALCULATE([Sales July 2015], PREVIOUSMONTH(DATESBETWEEN(Dates[Date], DATE(2015,7,1), DATE(2015,7,31))))

it works fine.

 

I don't get how the time intelligence functions are working. What am I missing??

 

 

 

 

 

 

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]

3 Replies

  • 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