[SOLVED] two days before Previousday dax

two days before Previousday dax

I have added a measure to calculate difference to yesterday's figures however when it is Monday's figure it returns blank instead of comparing against Friday's figure.

Is there a way of asking sales figure from two days ago, I tried deducting 2 as per

previous Day Sales:=CALCULATE(
sum(Sales[1]),
PREVIOUSDAY(Calender[Date]-2))

But it didn't work

Any suggestions please?

Thanks, B

Solution

Assuming in your context is present Sales[Date] or Calendar[Date] column you can get the previous date with sales by using a FILTER:

Previous Day Sales :=
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        ALL ( Sales ),
        Sales[Date]
            = CALCULATE (
                MAX ( Sales[Date] ),
                FILTER (
                    ALL ( Sales ),
                    COUNTROWS ( FILTER ( Sales, EARLIER ( Sales[Date] ) < Sales[Date] ) )
                )
            )
    )
)

This is an example in Power BI, but works in Power Pivot (Excel) too.