SOLVED

Power Query - Creating Time Based DAX Measure

%3CLINGO-SUB%20id%3D%22lingo-sub-2647174%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Creating%20Time%20Based%20DAX%20Measure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2647174%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20working%20with%20Power%20Query%20in%20Excel%20(Office%20365%20version).%20I%20have%20a%20data%20model%20where%20I%20have%20a%20bunch%20of%20financial%20data%20for%20different%20years%20and%20different%20quarters.%20When%20it%20comes%20to%20anything%20date%20related%2C%20the%20source%20data%20has%20just%20two%20columns%20-%201%20with%20a%20four%20digit%20year%20(YYYY)%20and%201%20with%20a%20single%20digit%20quarter%20number%20(1%2C%202%2C%203%2C%20or%204).%20A%20lot%20of%20data%20that's%20in%20quarter%204%20is%20actually%20aggregated%20year-end%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20trying%20to%20create%20a%20measure%20where%20I%20sum%20up%20a%20column%20but%20for%20a%20previous%20year.%20Ran%20into%20a%20bunch%20of%20trouble%20trying%20to%20do%20that%20and%20learned%20that%20when%20working%20with%20Power%20Query%2C%20if%20you%20want%20to%20do%20any%20date-related%20calculations%20then%20you%20should%20create%20a%20date%20table%20and%20configure%20a%20relationship%20(a%20join)%20with%20your%20data%20table.%20So%20I%20made%20a%20date%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I'm%20lost%20as%20to%20how%20to%20go%20about%20joining%20the%20date%20table%20with%20my%20data%20table%20and%20then%20correctly%20formulating%20a%20measure%20to%20get%20what%20I%20need.%20I%20know%20the%20date%20table%20is%20supposed%20to%20be%20joined%20with%20the%20data%20table%20based%20on%20a%20date%20but%20how%20should%20I%20go%20about%20getting%20a%20date%20in%20my%20data%20table%20with%20just%20the%20year%20and%20quarter%20number%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20need%20the%20measure%20to%20sum%20up%20one%20of%20the%20columns%2C%20but%20for%20the%20previous%20year%20and%20it%20also%20needs%20to%20only%20be%20for%204th%20quarter.%20If%20it%20includes%20any%20other%20quarter%20then%20it%20will%20be%20counting%20things%20more%20than%20once.%20I%20know%20I%20would%20be%20able%20to%20use%20certain%20DAX%20functions%20like%20PREVIOUSYEAR%20or%20DATEADD.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2647174%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2647503%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Creating%20Time%20Based%20DAX%20Measure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2647503%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128131%22%20target%3D%22_blank%22%3E%40Cisso10%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edid%20you%20do%20a%20search%20on%20the%20internet%20about%20the%20subject%20matter%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2647537%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Creating%20Time%20Based%20DAX%20Measure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2647537%22%20slang%3D%22en-US%22%3EYes%2C%20I'm%20going%20through%20lots%20of%20different%20videos%20and%20websites%20but%20one%20problem%20I%20keep%20running%20into%20is%20they%20all%20use%20Power%20BI%20which%20is%20a%20little%20different%20from%20the%20user%20experience%20in%20Excel.%20So%20I%20just%20keep%20trying%20to%20figure%20it%20out%20using%20instructions%20and%20tutorials%20based%20on%20Power%20BI%20since%20they're%20still%20similar.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2647538%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Creating%20Time%20Based%20DAX%20Measure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2647538%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128131%22%20target%3D%22_blank%22%3E%40Cisso10%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20power%20query%20is%20a%20query%20tool%20to%20prep%20dataset%20and%20DAX%20has%20everything%20to%20do%20with%20Data%20Modeling%20and%20not%20Power%20Query.%26nbsp%3B%20The%20M%20Language%20is%20associated%20with%20Power%20Query%2C%20and%20DAX%20Language%20is%20associated%20with%20Data%20Modeling.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3De-CFYi52gpc%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EHighline%20Excel%202016%20Class%2022%3A%20How%20To%20Build%20Data%20Model%20%26amp%3B%20DAX%20Formulas%20in%20Power%20Pivot%20-%20YouTube%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsocial.technet.microsoft.com%2Fwiki%2Fcontents%2Farticles%2F1018.powerpivot-grouping-by-dates-months-quarters-years-etc-in-pivottables.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EPowerPivot%3A%20Grouping%20By%20Dates%20(Months%2C%20Quarters%2C%20Years%2C%20etc.)%20in%20PivotTables%20-%20TechNet%20Articles%20-%20United%20States%20(English)%20-%20TechNet%20Wiki%20(microsoft.com)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

6 Replies

@Cisso10 

 

did you do a search on the internet about the subject matter?

Yes, I'm going through lots of different videos and websites but one problem I keep running into is they all use Power BI which is a little different from the user experience in Excel. So I just keep trying to figure it out using instructions and tutorials based on Power BI since they're still similar.

@Cisso10 

 

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

 

PowerPivot: Grouping By Dates (Months, Quarters, Years, etc.) in PivotTables - TechNet Articles - Un...

best response confirmed by Cisso10 (New Contributor)
Solution

@Cisso10 

Let assume your table looks like

image.png

Transforming it in Power Query add column with last date of each quarter

image.png

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

image.png

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

image.png

This works, thanks a bunch!

@Cisso10 , you are welcome