Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Trouble with DateAdd

 Hello folks,

I think I do have an understanding issue with the DateAdd function.

 

I have a table with different articles and prices per month.

My aim is to display the articles in a matrix visual, where the columns contain the Date and the prices. Additionally, I would like to display the difference per month in %.

So I wrote following measure:

 

DifChange =
Var Prev = CALCULATE(SUM(Table[Price]), DATEADD(Table[PriceDate],-1, MONTH))
Var Now= SUM(Table[Price])

return (Now-Prev)/Prev
 
But I do receive an error due to the DateAdd funtion. It does not work in a matrix, only when I restrict the Matrix to one Date.
I tried also to connect the PriceDate to a full calendar table where the dates are set. But in this case, the returned value is always 0.
This is the way the table looks: 
ArticlePriceDatePrice
ArtA01.06.20241,6
ArtA01.07.20241,61
ArtA01.08.20241,62
ArtB01.06.20240,5
ArtB01.07.20240,55
ArtB01.08.20240,56
ArtC01.06.20246,5
ArtC01.07.2024

6,5

 

And this is the way I want it to look like:

Article01.06.2024Dif01.07.2024Dif01.08.2024Dif
ArtA1,601,610,6%1,620,6%
ArtB0,500,559,1%0,561,8%
ArtC6,506,50,0%  

 

Why is the measure wrong? 

 

Thanks in advance for your support!

 
Need Help
Tips and Tricks
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    Anonymous 

    The issue arises because the DATEADD function expects the PriceDate column to be part of a properly related Date table, and it also needs the matrix visual to display data with proper context for each month. If you restrict the matrix to one Date, the measure works because there's a single context. Without that, DATEADD doesn't behave as expected.

     

    Solution:

    Create a Date table in your model with continuous dates spanning all possible PriceDate values.
    Relate this Date table to the Table[PriceDate] column.

     

    Date Table:

    DateTable = 
    ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM YYYY"),
    "MonthNumber", MONTH([Date])
    )

    Updated Measure:

    DifChange = 
    VAR PrevPrice =
    CALCULATE(
    SUM(Table[Price]),
    DATEADD('DateTable'[Date], -1, MONTH)
    )
    VAR CurrentPrice = SUM(Table[Price])
    RETURN
    IF(
    NOT ISBLANK(PrevPrice),
    DIVIDE(CurrentPrice - PrevPrice, PrevPrice, 0),
    0
    )

    Use Article as rows.
    Use DateTable[Date] as columns.
    Use two measures in the values section:
    SUM(Table[Price]) for prices.
    DifChange for the percentage difference.

     

    💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

2 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Anonymous 

    The issue arises because the DATEADD function expects the PriceDate column to be part of a properly related Date table, and it also needs the matrix visual to display data with proper context for each month. If you restrict the matrix to one Date, the measure works because there's a single context. Without that, DATEADD doesn't behave as expected.

     

    Solution:

    Create a Date table in your model with continuous dates spanning all possible PriceDate values.
    Relate this Date table to the Table[PriceDate] column.

     

    Date Table:

    DateTable = 
    ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM YYYY"),
    "MonthNumber", MONTH([Date])
    )

    Updated Measure:

    DifChange = 
    VAR PrevPrice =
    CALCULATE(
    SUM(Table[Price]),
    DATEADD('DateTable'[Date], -1, MONTH)
    )
    VAR CurrentPrice = SUM(Table[Price])
    RETURN
    IF(
    NOT ISBLANK(PrevPrice),
    DIVIDE(CurrentPrice - PrevPrice, PrevPrice, 0),
    0
    )

    Use Article as rows.
    Use DateTable[Date] as columns.
    Use two measures in the values section:
    SUM(Table[Price]) for prices.
    DifChange for the percentage difference.

     

    💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Kedar_Pande  Thank you so much for the explanation, that worked so fine. Now I understand, that there is a context needed so DateAdd could work. I have even added a Sort Column to show the values chronologically. Have a nice day!