DATEADD -1 month


#1

I have a table with a single row of data per project per period end date (last day of a month) and need to compare this figure with the previously submitted figure. My problem is two fold. If a project had a submission of data every month I had hoped to simple use dateadd within a calculate statement as below

EFV Last Month =
CALCULATE(
sum(DataCapture[EFVAMOUNT]),
dateadd(DataCapture[PERIODDATE],-1,MONTH))

However this returns blank values when the number of days in a month change (i.e. if all months had 31 days life would be simple)

Solving this would be great

But also - what happens when a project fails to submit data in a month… I then need to get the last submitted EFVAMOUNT from this project - which could be a number of months prior

Any suggestions?


#2

Whenever using time intelligence functions, you always want these to include parameters from your date table. This prevents many issues like this.

If you just want to find the last submitted date, then utilising a technique like the below is the way to go.