Forum Discussion

SealDog's avatar
SealDog
Regular Visitor
8 years ago

Help with time intelligence for a newbie

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....
Need Help
bullius's avatar
bullius
Helper V
8 years ago

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's avatar
SealDog
Regular Visitor
8 years ago
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's avatar
    bullius
    Helper V
    8 years ago

    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