Hi, I have some data feeding into PowerQuery that I will transform and present in a Power Pivot.
I want to put dates across the columns. The raw data has everything in Months. We want to see the first 6 months or so as months but then group into Qtrs and then group into half years or full years. I think we could use the Excel grouping, but that needs some management on a monthly basis and we would like something that we can feed the months into and the output is effectivelly the groups.
Attached I have 3 sheets for Jan, Feb and Mar as in each Month we need different groups. It is not the case that it is always the first 6 as months and then qtrs as we only want to group into whole quarters. So I am trying to think about the condition if the first month is the 1st, 2nd or 3rd month of the current quarter. So that you will see in the 3 sheets with the expected output column.
Can anyone suggest a solution please?
Hi Mark,
In Power Pivot you should create a date table. You can then add a column that classifies each date into the periods you want (your Output column in the example file). You can use an IF formula that uses today's date to test if the period should be the current month, a quarter or a half year, that way it auto updates.
This post on fiscal dates should give you some ideas. In Power Pivot we don't have CHOOSE but you can use SWITCH instead. I hope this points you in the right direction. Let me know if you get stuck by suppyling a sample file with your attempt.
Mynda
Thanks I had a go following your video and got something working. I am not sure if it is the most efficient code, but it does work, so that solves a problem. My solution is attached. Thanks again
Hi Mark,
That's one way. I think you need to change the quarters to state the year before the quarter number otherwise they won't sort properly in the PivotTable. e.g. 21Q1, 21Q2 etc.
Mynda
You are right! Thanks for highlighting!
