DATEADD Month-1 only partially working and I can't understand why

[Anonymous]

GrReetings all

I have only been working in PowerBI for 2 weeks and can't seem to understand the following:

I am trying to show the difference between a value for one month and the previous month and am using this measure on the table to line them up next to each other:

MonthEndDate	ID	Balance
31 May 2019	123	150
30 June 2019	123	200
31 July 2019	123	250
31 August 2019	123	250
30 September 2019	123	150

Value Previous Month = CALCULATE(SUM('table1'[Balance]),DATEADD('table1'[MonthEndDate],-1,MONTH))

I exspect to see the following:

MonthEndDate	ID	Balance	Value Previous Month
31 May 2019	123	150
30 June 2019	123	200	150
31 July2019	123	250	200
31 August 2019	123	250	250
30 September 2019	123	150	250

Instead, some months are mysteriously missing from the visual (shows as blank in the [Value Previous Month] measure;

The following months are missing across the entire dataset, specifically Feb, April, June, September, November.

For some months most of the values show and for some months most of the values are blank.

Not knowing PowerBI, I thought it might have something to do with the fact that some months end on 28, 29, 30 or 31, so "-1 MONTH", if taken by day, might not exist in the data, but changing the day to 01 across the board didn't resolve the issue.

Any suggestions would be appreciated.

[az38]

Hi @Anonymous 

try to use EOMONTH and FILTER like

Value Previous Month = CALCULATE(SUM('table1'[Balance]),filter(all(Table1),Table1[MonthEndDate]=EOMONTH(SELECTEDVALUE('table1'[MonthEndDate]),-1)))

[Anonymous]

Hi @az38 

Thank you for that. It returns results on all rows but the numbers are much higher than expected (e.g. 101 157 932 where I expected 779).

If I slice over date (e.g. 31 May 2019) all the "Value Previous Month" calculated values are the same for all the ID's.

How would I add ID to this measure to stop this from happening?

[az38 · Accepted solution]

@Anonymous 

if it should be grouped by allocaltionid also, you could try

Value Previous Month = CALCULATE(SUM('table1'[Balance]),
filter(all(Table1), 'table1'[AllocationId]=SELECTEDVALUE('table1'[AllocationId]) && Table1[MonthEndDate]=EOMONTH(SELECTEDVALUE('table1'[MonthEndDate]),-1)))

[Anonymous]

Thank you @az38 

That answers the question