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
Icon for Helper V rankHelper 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
      Icon for Helper V rankHelper 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