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. 

[Giaaa1220]

My goal is to show total gallon for each month, and % vs previous month. 

I don't know what is the best way to do it since there's different days in each month. 

[Tyro_abc]

Hi @Giaaa1220 

If you extract month from the dates and summarize total gallon at the month level, would that serve your purpose?

I added two extra rows   2020-10-30,  2020-11-30 to prove the concept. I simply ordered all the rows by month and then took the previous month's value to calculate the percentage difference using a Multi-row tool.

I used 'group by' year to do this operation only for a specific year, if you want to continue this for all years, that is, 2019 Dec should be used in 2020 Jan calculation, then you don't need the 'group by'.

[Qiu]

@Giaaa1220 
Following the idea of @Tyro_abc , I made something like this.

[Giaaa1220]

I don't need alteryx to product the vs previous month variance. I need the most granular data shown in the dataset - which is the current date (by day), the gallon, previous month day, previous month gallons, four columns.  If previous month have 31st, add 31 to the current day, show null in gallon, show previous month and previous month gallons.  and i dont want the duplicated 30 as stated earlier.

[echuong1]

Your dataset only contains 28 records, effectively 1 month data. There is no overlap in the time periods to show the current month, current gallon, previous month, and previous gallon. I added a couple more datapoints to show functionality.

Essentially, you can create "placeholder" dates by just changing the month value and concatenating values. You can then join this created date to the original post date field. Any sort of matches where you would have current month data and previous month data would come out of the J output. Anything that did not have a match would come out of the L and R outputs. They are essentially the same population since you joined on itself. This will be anything that did not match, including instances of 31st, where the previous month only had 30 days. You can use a union to include these still.

[Giaaa1220]

Thanks. Issue with your proposed idea is for example, April only has 30 days, so previous month March will get matched 30 days data (April 1st to April 30th) with March 31st missing.

[Giaaa1220]

I guess one way to fix is to add placeholder postdate '31st' in every month with null as gallon,so that we can make sure previous month include the 31st as well. ex. add 2020-11-31 to the data.... Does anyone know how to do insert these in a non manual way