Direct Query and Time Intelligence functions

Hi there,

I was facing the same problem and I came up with the following code as a work around.  These scripts calculate measures such as month over month (MoM) and year over year (YoY).

Last Years Amount

Amount_LY = 
    CALCULATE(
        [AMOUNT],EXPENSES[DATE_YEAR] = VALUES(EXPENSES[DATE_YEAR])-1)

Last Months Amount

Amount_LM = 
    IF( (VALUES(EXPENSES[DATE_MONTH])-1) =0,
        CALCULATE(
            [AMOUNT],
            EXPENSES[DATE_YEAR] = (VALUES(EXPENSES[DATE_YEAR])-1),
            EXPENSES[DATE_MONTH] = 12
        ),
        CALCULATE(
            [AMOUNT],
            EXPENSES[DATE_YEAR] = (VALUES(EXPENSES[DATE_YEAR])-0),
            EXPENSES[DATE_MONTH] = (VALUES(EXPENSES[DATE_MONTH])-1)
        )
    )

You can use these basic calculations to derive measures like growth rates and differences.  As you can see in the 'Last Month Amount' code, you will have to get creative at times to replicate the time intelligence based calculations.

I hope this helps,

Eric

---

Anonymous wrote:

Hi,

 

Power BI has very powerful Time Intelligence capabilities.  

 

However, they seem not to be applicable when using a direct query.

 

Am I missing something big?

 

What is the alternative to the Time Intelligence functions when using Direct Query?

 

Thank you

Tamir

---

From msft docs:  No built-in date hierarchy: When importing data, every date/datetime column will also have a built-in date hierarchy available by default. For example, if importing a table of sales orders including a column OrderDate, then upon using OrderDate in a visual, it will be possible to choose the appropriate level (year, month, day) to use. This built-in date hierarchy isn't available when using DirectQuery. If there's a Date table available in the underlying source, as is common in many data warehouses, then the DAX Time Intelligence functions can be used as normal.