Hi Simon,

I'm not sure I follow. Are you wanting to filter the data in Power Query to only return the data for the last 12 months? If so, I'm not sure you're going about it the right way.

Are you able to create a dummy sample file (or share your real file) with us so we can see what you're working with and a mock up of your desired result?

Mynda

Hi Mynda - really super of you to be so helpful! I am loving the course, I've been all the way through to understand the context and now I'm selectively going back over.

Sorry my question was not clear. I've tried a better explanation below. While I really appreciate your interest, I'm sure you won't want to spend much time on this pretty unusual situation. Don't worry, I won't be at all put out if you can't justify the time it takes to swim through my soup! 

My experimental file is too big for your upload limit. (as I'm learning, I'm leaving a lot of data in sheets I won't need in the final version) so I'll just upload an image of part of the page I'm trying to fix. On this tab, there are about 40 charts I prepare as part of the work I do to analyse government published Cloud Spend on a procurement platform called G-Cloud. As you will see from the snapshot, which shows the data in pivot tables and associated charts (3 are shown in the example) I monitor the last 5-years of spend looking at the monthly trends looking for anomalies.

The charts you can see here run from April to March. The data is produced monthly and so I want to produce the next month's charts running from May to April and the following month's data i want to look at running from June to May... so i am always comparing the full 12 months to the end of the current reporting period with the four previous 12 month periods.

I have worked out how to add columns to the data model manually using month numbers to get the pivot tables to order the data in the way I want. So I can automate this using one key variable: the last month number in the current data sets. This I've put in the query as two measures 

LastMMMYY:=MAX(Data[ReturnMonth])

LastMM:=MONTH([LastMMMYY])

As I need both the month number and the year of the last month to order the data for the pivot tables. Now I want to add a conditional column allocating a new month number to reflect the position of that month in the present 12-month period (So April in the April-to-March example will be '1' instead of '4') Here's an example of the M code I used in the manual way of building the column:

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "FY", each if [Month]>= 4
then [Year]-1999
else [Year]-2000),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each 1999 + [FY]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type text}, {"FY", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Custom", "StartFYear"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "FY-full", each [StartFYear]&"/"&[FY]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"FY-full", type text}}),
#"Add FMonth" = Table.AddColumn(#"Changed Type3", "FMonth", each if [Month]<= 3
then [Month]+9
else [Month]-3),
#"Changed Type4" = Table.TransformColumnTypes(#"Add FMonth",{{"FMonth", Int64.Type}})
in
#"Changed Type4"

So I am trying to us the measure for, in this case, the hard-coded '4', '3' etc. Maybe i should be using variables rather than measures?

Many thanks again and any ideas will be gratefully received,

Simon

Hi Simon,

Great to hear you're enjoying my course!

Your objective should be done in Power Pivot using the time intelligence functions. I'm not sure if you've learnt Power Pivot already but if not, you might like to consider my Power Pivot course.

Let me know if you have any questions. And if you get stuck, just create a small dummy file containing a small sample of your data in a Power Pivot model and we can try to help from there.

Mynda

Marvellous! I'd very much like to move onto your power pivot course, I'll try and munge the present problem while I thoroughly learn the PQ course first. 

Your style is absolutely amazing! I do video shorts to publicise stuff I blog about - but they are full of 'umms' and 'errs', even after I've written and learnt the script. How do you discipline yourself to be so concise and articulate? Quite brilliant, easily the best I've studied (and I've listened to many YouTubes)... 

Wow, thanks for your kind words, Simon! I've trained myself to pause rather than um-ing and ah-ing. Pauses are easy to edit out, whereas ums and ahs tend to join the previous word, which is much more difficult to remove. I also might record a video multiple times...it's usually quicker to re-record than to edit a video with lots of mistakes 🙂