Help with time intelligence for a newbie

[SealDog]

Hi all,

My understanding is that for DAX time intelligence functions to work your date table must be continuos. I.e a row for ever day and the other columns are for the metadata around that date e.g. Quarter week year.

My question is How does this work if the data set you have is by quarter I.e. Q1 Q2,etc. how would the relationship be created between the tables.?

I hope this makes sense.

Chers

[bullius]

Hi @SealDog

You are right in your understanding.

If your data set is by quarter, first, you need to make sure there is a quarter field in your date table. Then, create another table like this: 

Quarter =
SUMMARIZE (
    DateTable,
    DateTable[Quarter]
)

What this does is creates a table with unique values for each quarter.

Then, create a relationship between your data set and the Quarter table and between your date table and the quarter table.

Note: you may have to do the same thing, but with a field that combines year and quarter (e.g. Q1 2017) to get unique values per year and quarter. It depends how you are using the data.

Hope this helps!

[SealDog]

Thank you bullius that is very helpful and makes perfect sense. Could I just clarify the following
1. Which table / column do I refer to within DAX, I.e the qtr column from the main date table or the QTR from the new qtr table.
2. If as you mentioned I wanted to analyse per Q1 2017 etc would that be a second 'intermediate' date table.

Thank you again really appreciate your help.

[bullius]

1. Your relationship between the tables should be as follows:

QtrTable 1--->---* DateTable   or   QtrTable 1---<>---* DateTable (If you want to filter your transaction table by the DateTable)

QtrTable 1--->---* TransactionTable

Then, you use whichever table you want to filter by in the DAX query. 

2. Yes, use whichever works best for your analysis, or both.

Hope that helps

[miltenburger]

Just create a date table inside Power BI to link tables together