Previous Day Value

[RobertSlattery]

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.

[Greg_Deckler]

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?

[RobertSlattery]

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.

[Greg_Deckler]

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?

[RobertSlattery]

Yes, thanks @Greg_Deckler it makes sense, except that I thought the All() filter would remove that relationship. No?

May

[Greg_Deckler · Accepted solution]

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. 

[RobertSlattery]

Ok, in that case it now makes perfect sense. I've seen patterns where they say not to have any relationship between tables. Now I understand why.