Log In
Register
Home
Power Query
Power Pivot
July 11, 2016
Offline
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
November 8, 2013
Offline
Hi,
Have you tried:
=CUBEVALUE("ThisWorkbookDataModel",$B$5,$C8,D$6,"[Data].[Date (Year)].&["&D8&"]")
Change D8 to your cell where the Year value is.
Power Query
Power Pivot
July 11, 2016
Offline
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
November 8, 2013
Offline
Not following...
.[Date (Year)].&["&D8&"]")
In that cell D8, you can put any formula or dropdown with your year values, you need help with adding a dropdown in a cell?
Power Query
Power Pivot
July 11, 2016
Offline
Hi Catalin,
I understand what you are saying and it works fine for the "This Year" column. Is there anyway I can reference the same cell but deduct a year within the formula for the "Last Year" column?
I think I can work out how to do dropdowns in cells thanks 🙂
Bax
Trusted Members
Moderators
November 1, 2018
Offline
I think Catalin's point is that you can do something like:
=CUBEVALUE("ThisWorkbookDataModel",$B$5,$C8,D$6,"[Data].[Date (Year)].&["&D8-1&"]")
since you're just concatenating a value into a string.
1 Guest(s)
