Forum Discussion
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 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!
How about this one
Total Amount Last Year = CALCULATE ( [Total Amount], SAMEPERIODLASTYEAR ( 'Dates'[Date] ), ALL ( Dates ) )
8 Replies
Hi Nemes
Try this revision
Total Amount Last Year = CALCULATE ( [Total Amount], SAMEPERIODLASTYEAR ( 'Dates'[Date] ), ALLEXCEPT ( Dates, Dates[Year] ) )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.
How about this one
Total Amount Last Year = CALCULATE ( [Total Amount], SAMEPERIODLASTYEAR ( 'Dates'[Date] ), ALL ( Dates ) )
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.
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?
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.
