Log In
Register
Home
Power Query
Power Pivot
July 11, 2016
Offline
Hi,
I am trying to create a measure in a BI Matrix visual that picks up the values for the previous year based on the current selection. The values are in a table which is broken down by Country, Manufacturer and Year/Qtr. The Year/Qtr field in not in a date format it is a text field with the values shown as YYQQ. There are two value fields, Units and Mkt Units.
What I am trying to achieve is something like the following. The measure I am trying to create are the ones to show the Previous Year rows:
Yr/Qtr 21Q1 21Q2 21Q3 21Q4 22Q1 22Q2 22Q3
Units 18,734 15,209 16,179 16,158 12,179 14,105 17,747
Units Previous Year 18,734 15,209 16,179
Mkt Units 135,449 132,266 115,625 123,706 86,638 113,493 143,875
Mkt Units Previous Year 135,449 132,266 115,62
The above shows the total value for the year and quarter. In the visual I will need to be able to filter the values by Country and/or Manufacturer.
I have added an Excel file that has some sample data in it. It also has a summary like the above created with Excel formulas. You can use the dropdowns in I1 and K1 to filter the data as required.
Any pointers on how to do this in BI will be very gratefully received.
Thanks
Bax
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
In order to include the time intelligence you want, you must have real dates in the data table and make use of a calendar table in the data model. So, I quickly added a column (manually) with dates based on the column with YyQn. And I created a calendar table.Then you can create measures to calculate the same quarter last year.
See if you can follow what I've done in the attached workbook. It can be made prettier, but I didn't go so far.
1 Guest(s)
