Rolling graph with fixed number of Period

Power Query
1

Hi Team,
I would like to have a graph taking the data for last 12 months not including current month.
my data are live updated from ERP.

I added a column which can filter for last 12 months not including current month. But I got it from Jul 2019 to Nov 2020. Please see incorrect result sheet.
In the attached, I need Sep 2019 to Oct 2020 as we are now Nov 2020.

Please let me know how to fix the column formula or if there any way I can do to get this automated.
Many thanks.QRollingMonth.pbix (444.7 KB) TransactionsbyRollingDates.xlsx (1.1 MB)

2

Hi @ammu,

You can add a custom column with this M code to your date table, to create a Last 12M identifier.

image
image1010×306 25.7 KB

In your expected result you were actually showing 14 months not 12 but you can adjust the number you want visible quite easily…

image
image831×317 15.5 KB

I hope this is helpful.

2 Likes
3

Many thanks, Melissa,
This is an exact one I required.
Much appreciated.
Regards,
Aye

4

Hi Team,
I just want to add a bit more on this. I also require “Last 12 Months and Next 12 months” column and “Last 12 Months + Budget FY 2021” column.

I have attached the date table and expected result highlighted in Yellow. "M code with Power query will be a great help as I am doing manually at the moment.Q12Mths24Mths&12Mths+Bud.xlsx (94.3 KB)
Thank you.

5

@ammu,

please create a new topic, thanks.

6

thanks, @Melissa.
Regards,
Aye