How to get previous month gallon for each date

Giaaa1220

I have sample data like below. It has post date from 2019-1-1 until 2020-12-16. I want to add another column saying previous month date, and another column saying previous month gallon. 

I was using formula  DateTimeAdd([POST_DATE],-1,"month")  to get Last month date and doing the left join with itself.

But the problem is, date10/31 previous month date would become 9/30; 10/30 would also become 9/30. so it is duplicated. and since November last day is 11/30, previous month last day would be 10/30. So when I use the final output and compare November data with Oct data it wouldn't be accurate, oct data is missing the 31st data.

What would be a better way to do this?

POST_DATE	GALLON
9/26/2020 0:00	3
9/25/2020 0:00	4
9/24/2020 0:00	5
9/23/2020 0:00	6
9/22/2020 0:00	2
9/21/2020 0:00	7
9/20/2020 0:00	9
9/19/2020 0:00	3
9/18/2020 0:00	6.321429
9/17/2020 0:00	6.642857
9/16/2020 0:00	6.964286
9/15/2020 0:00	7.285714
9/14/2020 0:00	7.607143
9/13/2020 0:00	7.928571
9/12/2020 0:00	8.25
9/11/2020 0:00	8.571429
9/10/2020 0:00	8.892857
9/9/2020 0:00	9.214286
9/8/2020 0:00	9.535714
9/7/2020 0:00	9.857143
9/6/2020 0:00	10.17857
9/5/2020 0:00	10.5
9/4/2020 0:00	10.82143
9/3/2020 0:00	11.14286
9/2/2020 0:00	11.46429
9/1/2020 0:00	11.78571
8/31/2020 0:00	12.10714
8/30/2020 0:00	12.42857

echuong1

It makes sense from a data perspective that 10/31 and 10/30 would show 9/30 as the previous month, since that is the last day in the month. How are you looking to handle varying months? What are you trying to analyze them against? 

Days 1-30 (28/29 in the case of February) would be able to be compared, but how are you looking to compare the 31st days? Since every other month has an extra day, there is no direct comparison aside from aligning them with the 30th.