Forum Discussion

Nemes's avatar
Nemes
Frequent Visitor
8 years ago
Solved

Issues with measure using time intelligence

Hello,   I've been running into issues when creating measures, particularly with time intelligence (for example SAMEPERIODLASTYEAR).   Here is a stripped down version of my report, with only the ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Nemes

     

    How about this one

     

    Total Amount Last Year =
CALCULATE (
    [Total Amount],
    SAMEPERIODLASTYEAR ( 'Dates'[Date] ),
    ALL ( Dates )
)
Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

You've built the wrong relationship.  In the transactions table, create this calculated column formula

 

=DATE(LEFT(Transactions[date_key],4),MID(Transactions[date_key],5,2),RIGHT(Transactions[date_key],2))

 

Edit your relationship to know connect this Date column to the Date column in the Dates Table.

 

Just do this much and your measure will work without any editing.

 

Hope this helps.

 

Nemes's avatar
Nemes
Frequent Visitor
8 years ago

Hi Ashish_Mathur,

 

This also works, although like the inclusion of the optional ALL(Dates) filter, I don't understand why this step is required.

 

Usually, using a date key of format YYYYMMDD is the recommended practice for a datawarehouse. Is there some sort of limitation in Power BI for date relationships where it needs to be an actual date field instead of a surrogate key of format YYYYMMDD?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    PowerBI's data and time intelligence functions only work with actual date entries.  Which is why your formula (without any editing) started working when i converted a text entry into a Date and then built the relatiosnhip.