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.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
Need Help

4 Replies

  • 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