Forum Discussion

TePe's avatar
TePe
Helper IV
7 years ago

Time Intelligence with Direct Query

Hi,   I know about the limitations of Time Intelligence when using Direct Query (i.e. you don't see Time Intelligence in the Quick Measures). However digging into the documentation (https://docs.mi...
Anonymous's avatar
Anonymous
Not applicable
7 years ago
Mate, your DW Date table has to follow exactly the same rules as the Date table in PBI has to. If that's not the case, forget about any time-intel calculations.

Best
Darek
  • TePe's avatar
    TePe
    Helper IV
    7 years ago

    Darek,

     

    thanks for you feedback. When I look at https://docs.microsoft.com/en-us/power-bi/desktop-date-tables I only see that I need to mark my DimDate as the date table (the scenario I talked about when I had separate date and fact tables). That's what I did, the assistant is so nice to check field setting and data quality in the date table on its own... Still no Time Intelligence in Quick Measures and still DATESYTD works...

     

    Best Regards,

     

     

    Thomas

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      Is you Date table marked as such in PBI? Does it join to the fact table on the right field in the model? Does it cover all the years in full that are present in the columns to which it's joined? If the conditions are satisfied, then you should have no problem with the time-intel functions.

      Best
      Darek
      • TePe's avatar
        TePe
        Helper IV
        7 years ago

        HI,

         

        so what I did:

        - created an Azure Data Warehouse with AdventureWorks DW sample DB

        - deleted 2006 data from dimDate because there's a gap in the date table, Power BI isn't happy with that. There's no sales data for 2006...

        - created a DirectQuery model with Power BI latest version

        - selected DimDate and FactInternetSales

        - built up a relationship between FactInternetSales.OrderDateKey and DimDate.DateKey

        - I marked DimDate as Date Table with FullDateAlternateKey as the date field

        - Tried to create a Quick Measure with Time Intelligence but the corresponding section isn't available in the calculation list

         

        What works is DAX formulas like DATESYTD() but this is independent of using a separate date table or not. So still I'm confused what extra functionality should be enabled with a date table in the DirectQuery source compared to anything else...

         

        THANKS

         

         

        Thomas