Forum Discussion
Previous Day Value
Why does this work...
Order Value Prev Day = CALCULATE(
SUMX(orderLines, [Amount] * RELATED(Order_Types[Sign])),
FILTER(
orderLines,
[Date] = MAX('Dim Date'[Date])
),
ALL(orderLines)
)But this is all blanks?
Order Value Prev Day = CALCULATE(
SUMX(orderLines, [Amount] * RELATED(Order_Types[Sign])),
FILTER(
orderLines,
[Date] = MAX('Dim Date'[Date]) - 1
),
ALL(orderLines)
)This is a measure and I'm using it in a Table whith 'Dim Date'[Date]
I have this table for the date dimension...
dim Date = ADDCOLUMNS(CALENDAR([From Date], [To Date]),
"Year", YEAR([Date]),
"Date Key", VALUE(FORMAT([Date], "YYYYMMDD")),
"FY",
VAR m = MONTH([Date])
VAR y = YEAR([Date])
RETURN IF(m > 6, y + 1, y)
)I'm not using the date hierarchy.
Nope, ALL just removes all of the context filters but not the relationships. Let me see if there is a way to do that but I'm not sure.
6 Replies
Tough to say without source data to replicate but my guess is that when you take the MAX date in the date key table and add 1 to it, you no longer have any dates that match it? If you wanted previous day, wouldn't you want -1?
Yes, sorry, rushed it, should be - 1. Edited to fix.
In answer to your question, I do have dates one day less for all the values selected and I use ALL(orderLines) to open up the context to the whole date range.
Again, tough to say without the data to replicate. But, if you have a relationship between your two tables based on Date. Then you will never have a match between them where date in one table matches -1 date from another table. Does that make sense?
