Issues with measure using time intelligence

[Nemes]

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 required tables and fields: Simple Measure.zip

These are the two measures I am using:

Total Amount = SUM('Transactions'[Amount])

Total Amount Last Year = 
CALCULATE(
 [Total Amount]
 ,SAMEPERIODLASTYEAR('Dates'[Date]) 
) 

When I try to show the "Total Amount Last Year" measure, I get no results for the "Total Amount Last Year" column (although the grand total has the value).

As far as I can tell, the the dates in my tables are contiguous, and there are no duplicates. The Date field is also a Date/Time data typ.

I've tried the same measures using the Contoso sample, and the Contoso report works exactly as it should, so I have a feeling there is something wrong with how my date table is configured.

Any help would be greatly appreciated!

[Ashish_Mathur]

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]

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]

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.

[Zubair_Muhammad]

Hi @Nemes

Try this revision

Total Amount Last Year =
CALCULATE (
    [Total Amount],
    SAMEPERIODLASTYEAR ( 'Dates'[Date] ),
    ALLEXCEPT ( Dates, Dates[Year] )
)

[Nemes]

Hi @Zubair_Muhammad,

This does work without filters, but it does not work as well once I use some filters. For example, if I want to see just 2015 and 2016, the "Total Amount Last Year" for 2015 will be blank, even though there were amounts in 2014.

Even if I was not filtering on years, it's bizarre that I would need to resort to ALLEXCEPT, whereas the Contoso sample report works without ALLEXCEPT. Would you have any ideas why there is a difference?

And thank you for your assistance.

[Zubair_Muhammad · Accepted solution]

@Nemes

How about this one

Total Amount Last Year =
CALCULATE (
    [Total Amount],
    SAMEPERIODLASTYEAR ( 'Dates'[Date] ),
    ALL ( Dates )
)

[Nemes]

That does seem to work! I thought I tried this before, but I think I tried ALL('Dates'[Date]) instead of ALL(Dates). I'm not sure why specifying only the Date column as an argument to ALL doesn't work, considering thre are no date duplicates.

Would you know why I need to specify the ALL() function, unlike all of the other how-to's and guides that I've found on this subject?

[Zubair_Muhammad]

Hi @Nemes

I will get back to you