Data with NO DATES, only year/quarter

[sqlgrl]

Hi All-

I've been working with PowerBI off and on for about a year.  I learn a little and then come back to it.  IU has just recently expanded licensing, and now I am getting pressured to really bone u  p and do some things.My task at hand is pretty simple (at least in my mind) - I want a report that has a table and a couple of line charts.  I have typically done ALL my change work in SQL (prior to this, in both SSRS & powerbi), but I've been reading that there are some nifty DAX tricks for quarter to quarter changes over time.  I created a concatenated field in sql to pull them together.  The ultimate goal is to be able to select a year/quarter, geography, and an industry, and then be able to see 2 line charts that 1) show wages over time - easy .. and 2) that show quarter to quarter change over time and/or YQtYQ (i.e. 2020q1 to 2021q1 OR 2021q1 to 2020q4 (previous quarter))

My data look like below:

The issue (for me, it seems) is that my existing data are only stored with year & quarter in separate fields.  The data are collected quarterly.   I have successful created a dates table using code I found online that has a date column, as well as a year, quarter and YearQuarter column.  I connected it to my excisting quarterly wage data:

I then tried to create a new measure called PQ_Wages (to see if it would grab the previous quarter and then put it in a column next to the current quarter.  I was able to create the measure, without error, but, it is blank - with no data in it - so I have NO idea what I'm missing.  I feel like there is some key information here that I am missing.

PQ_wages = CALCULATE(SUM(WagesOverTime_equi[wages]), PREVIOUSQUARTER('Date'[Date]))

Any advice/help would be greatly appreciated and please forgive me if I came to the wrong place. 

All the best,

sqlgrl since 1993 (aka Bethany)

[AlexisOlson · Accepted solution]

I'm not sure time intelligence will work correctly if your relationship isn't hooked up to the Date[Date] column.

Can you define a calculated column to give a representative date for each yearQtr that you could then relate to the Date table in a more normal 1-to-many relationship? You can do this in DAX along the lines of

QtrStartDate = DATE ( [year], 3 * ( [qtr] - 1 ) + 1, 1 )

If you can't do this in DAX due to DirectQuery limitations, you could try to do it at the source (SQL?) instead.

[sqlgrl · Accepted solution]

That WORKED!!!  All I needed to do was create an actual date in MY data ..... aaahsaaah.  All the things you don't know ... 😉  Really appreciate the lead!

[AlexisOlson · Accepted solution]

I'm not sure time intelligence will work correctly if your relationship isn't hooked up to the Date[Date] column.

Can you define a calculated column to give a representative date for each yearQtr that you could then relate to the Date table in a more normal 1-to-many relationship? You can do this in DAX along the lines of

QtrStartDate = DATE ( [year], 3 * ( [qtr] - 1 ) + 1, 1 )

If you can't do this in DAX due to DirectQuery limitations, you could try to do it at the source (SQL?) instead.

[sqlgrl]

Let me try that.  Interesting.  So in my existing data from my sever, create a date so that it has something to match to the Date table.  Not sure why I thought the year/quarter would handle that but maybe because it's a many to many, instead of one to many.  I'll report back - thank you for your quick response!