Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Helper V
4 years ago
Solved

Error with DateAdd

Dear members Hello   I am calculating the Current Month Sales amount: CM Sales = CALCULATE(SUM(Sales[Sales])) Also the Previous Month Sales PM Sales = CALCULATE(SUM(Sales[Sales]), DATEADD(Time[M...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Dimitris_Kats ,

     

    Try to sumx your original measure to get correct result in total.

    PM Sales with correct Total = SUMX(VALUES('Time'[Month]),[PM Sales])
    Sales Dif with correct Total = SUMX(VALUES('Time'[Month]),[Sales Dif])

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Super User
4 years ago

Dimitris_Kats , You should always use a date table for that

 

PM Sales = CALCULATE(SUM(Sales[Sales]), DATEADD(Date[Date], -1, Month))

 

refer

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4


To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • Dimitris_Kats's avatar
    Dimitris_Kats
    Helper V
    4 years ago

    Hello amitchandak  and thank you very much for your reply.

    I am using a date table for this measure . I use the month column from the date table wich is formatted as date. 
    This is not the problem 🙂

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Dimitris_Kats 

      The explanation provided by amitchandak is correct. DATEADD deals with a column of unique date values. The month column even if formatted as date is not unique. Use the date column with DATEADD or YearMonth Number column and subtract 1