Ok, I have a scenario where there is a rolling report updated every month. I need to add up the totals from the last 48 months (as it happens) but in the dummy file attached I have used 12 months. So in R the first total covers Jan-18 to Dec-18 but then the second total in column S covers Feb-18-Jan-19. I could probably figure it out with formulas but is there a way to do that in Power Query? Ideally I'd be happy just for the data to show the current last 12 months and then do the calculations in Excel. I just want a couple of pointers :-)Thanks as always
Hi Anne,
The file is corrupted, can you upload again?
There can be many ways to do that. For example, you can add another column, to return true or false depending if that Date falls in the desired range:
=if [Date] >= Date.AddMonths(List.Max(#"PreviousStep"[Date]),-48) then true else false
This new column can be used as a slicer in the report, if you select true, only the data needed will be displayed in the report.
Hm, I will upload the file again - would there be any way to have it completely in the background so that all the user has to do is refresh the data to show the last 48 months.
ok, i added the column I mentioned, had to unpivot first, as your data was on a horizontal layout.
