Aug 13 2021 03:39 PM
I'm working with Power Query in Excel (Office 365 version). I have a data model where I have a bunch of financial data for different years and different quarters. When it comes to anything date related, the source data has just two columns - 1 with a four digit year (YYYY) and 1 with a single digit quarter number (1, 2, 3, or 4). A lot of data that's in quarter 4 is actually aggregated year-end data.
I was trying to create a measure where I sum up a column but for a previous year. Ran into a bunch of trouble trying to do that and learned that when working with Power Query, if you want to do any date-related calculations then you should create a date table and configure a relationship (a join) with your data table. So I made a date table.
But I'm lost as to how to go about joining the date table with my data table and then correctly formulating a measure to get what I need. I know the date table is supposed to be joined with the data table based on a date but how should I go about getting a date in my data table with just the year and quarter number?
I then need the measure to sum up one of the columns, but for the previous year and it also needs to only be for 4th quarter. If it includes any other quarter then it will be counting things more than once. I know I would be able to use certain DAX functions like PREVIOUSYEAR or DATEADD.
Aug 13 2021 07:32 PM
Aug 13 2021 08:39 PM
Aug 13 2021 08:46 PM - edited Aug 13 2021 09:48 PM
First of all, power query is a query tool to prep dataset and DAX has everything to do with Data Modeling and not Power Query. The M Language is associated with Power Query, and DAX Language is associated with Data Modeling.
Highline Excel 2016 Class 22: How To Build Data Model & DAX Formulas in Power Pivot - YouTube
Aug 14 2021 04:13 AM
SolutionLet assume your table looks like
Transforming it in Power Query add column with last date of each quarter
Assign proper types for each column and load result to data model.
Assuming you already have Date table in data model create relationships with them
If Date was created by Power Query don't forget to mark it as Date Table in Design tab of Power Pivot.
DAX measures could be
Sales:=SUM(Table1[Value])
PY Sales:=CALCULATE([Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
or whatever you need. All dates related fields you take only from Date table, the rest is from your fact table.
Build PivotTable using your measures
Aug 14 2021 04:13 AM
SolutionLet assume your table looks like
Transforming it in Power Query add column with last date of each quarter
Assign proper types for each column and load result to data model.
Assuming you already have Date table in data model create relationships with them
If Date was created by Power Query don't forget to mark it as Date Table in Design tab of Power Pivot.
DAX measures could be
Sales:=SUM(Table1[Value])
PY Sales:=CALCULATE([Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
or whatever you need. All dates related fields you take only from Date table, the rest is from your fact table.
Build PivotTable using your measures