
Power Query
Power Pivot

July 11, 2016

Hi,
First of all thanks for the recent post on Cube functions, really helpful. I have now started to investigate using these as I can see lots of uses.
Inevitably this has prompted some questions. The first one is how can I automatically get the formulas to pick up a year and month based on a cell entry.
I want to create a report that shows data for the current year and a specified month and then next to that show the data for the same month but for the previous year. I want to allow the user to select the year and month they want to look at and then the data changes based on these choices.
I have used your Cube functions file (I hope you don't mind) and updated it to show the data for January 2021 and next to it January 2020. The file is attached. I have done this by specifically referencing the year within the formula. The month is currently picked up from a header cell but again the month is specifically referenced in the formula. The following shows the formula as it is. Cell D6 is the month.
=CUBEVALUE("ThisWorkbookDataModel",$B$5,$C8,D$6,"[Data].[Date (Year)].&[2021]")
Ideally what I would like to do is to have a list of years and months that the user can select from and then these are used in the formulas.
If I can work this out I can see a bright future for Cube functions in my reporting set up!
Thanks
Bax

Power Query
Power Pivot

July 11, 2016

Hi Catalin,
Thanks for the reply. I know that you can reference a cell as you have but what I was hoping was to have the user select a year and month and then the This Year and Last Year columns automatically update from their selection. I am thinking of something like a validated list for year and one for month but can't work out how to do this. For the year is it possible to somehow do subtraction within the formula so you could say Last Year = This Year - 1.
Thanks
Bax
1 Guest(s)
