May 2, 2014
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
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.
1 Guest(s)