Trouble with DateAdd

[Heidilein]

 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: 

Article	PriceDate	Price
ArtA	01.06.2024	1,6
ArtA	01.07.2024	1,61
ArtA	01.08.2024	1,62
ArtB	01.06.2024	0,5
ArtB	01.07.2024	0,55
ArtB	01.08.2024	0,56
ArtC	01.06.2024	6,5
ArtC	01.07.2024	6,5

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

Article	01.06.2024	Dif	01.07.2024	Dif	01.08.2024	Dif
ArtA	1,6	0	1,61	0,6%	1,62	0,6%
ArtB	0,5	0	0,55	9,1%	0,56	1,8%
ArtC	6,5	0	6,5	0,0%

Why is the measure wrong? 

Thanks in advance for your support!

[Kedar_Pande · Accepted solution]

@Heidilein 

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

[Heidilein]

@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!