Create a measure to calculate the previous date from another Date column in the same table.

[Anonymous]

Hi All,

I am stuck with a requirement which appears to be deceptively simple. I have the following table:

For every row, I want to calculate the previous date. Example in the New column below.

I tried with DATEADD. But, this expects a table of Dates.

Link to Excel is here.

Link to Power BI with this Excel is here

Any suggestions?

Please, I am working on an online model, hence, I cannot created calculated columns or additional tables.

Thanks,

Sau

[Anonymous]

The following measure appears to work

PreviousDate = 
var tbl=DATEADD(Sales[date],-1, DAY)
return FIRSTDATE(tbl)

[ebeery · Accepted solution]

@Anonymous how about something like this?

Previous Day Measure = 
Var _SelectedDay = SELECTEDVALUE('Table'[date])
Var _PreviousDay = _SelectedDay - 1
VAR _Result =
IF(
    HASONEVALUE('Table'[date]), _PreviousDay
)
Return
_Result

[Anonymous]

Thanks for replying @ebeery 

Before I accept your solution, I have just one question.

The syntax that you have used for calculating the previous date - is this the standard way to compute date additions. i.e. if I were to add 365, would it give me next year?

Var _PreviousDay = _SelectedDay - 1

[ebeery]

@Anonymous to be honest, I'm not enough of a DAX expert to answer that definitively.  It seems to work well in my testing but possibly there are edge cases out there on which it would fail.