cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sqlgrl
New Member

Data with NO DATES, only year/quarter

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:

sqlgrl_0-1638295036925.png

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:

sqlgrl_1-1638295404150.png

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)

 

 

2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

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.

View solution in original post

sqlgrl
New Member

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!

 

sqlgrl_0-1638296968925.png

 

View solution in original post

3 REPLIES 3
sqlgrl
New Member

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!

 

sqlgrl_0-1638296968925.png

 

AlexisOlson
Super User
Super User

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.

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!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!