Forum Discussion
Card showing "Blank" when using Date table
Hallo
I have two tables
- Orders which contains sale orders' amounts and dates along with a datekey (yyyyMMdd)
- Calendar with dates ranging beyond the sale orders' dates, with a corresponding datekey column; no missing date values in the interval.
- They have an active relationship, 1:* Calendar > Orders
I added a YTD measure to the Orders:
Orders YTD = TOTALYTD( SUM(Orders[Amount]) ; Calendar[Date] );
When I use it within a Card visual, I get a "(Blank)" value
Everything shows ok If I swap the Calendar[Date] with a Orders[Date], like this:
Orders YTD = TOTALYTD( SUM(Orders[Amount]) ; Orders[Date] );
What am I missing?
Thanks in advance
The issue is likely that you need to tell PBI what year it is, either by using a page/report level filter in PBI or a slicer in the report itself. The time-intelligence functions in DAX will default to the max year in your dates table if you do not tell PBI what year it is. Therefore, if you have the calendar table that goes out to 2018, the measure will show blank unless you tell PBI that the year is 2017.
Here's a little extra two cents, but I always prefer to have a trimmed calendar table. What I mean is, you can trim your calendar to not have dates that go beyond the max value you have in your sales fact table.
2 Replies
TheOckieMofoResolver II
The issue is likely that you need to tell PBI what year it is, either by using a page/report level filter in PBI or a slicer in the report itself. The time-intelligence functions in DAX will default to the max year in your dates table if you do not tell PBI what year it is. Therefore, if you have the calendar table that goes out to 2018, the measure will show blank unless you tell PBI that the year is 2017.
Here's a little extra two cents, but I always prefer to have a trimmed calendar table. What I mean is, you can trim your calendar to not have dates that go beyond the max value you have in your sales fact table.
- Greg_Deckler
Community Champion
Hmm, perhaps add an additional parameter to TOTALYTD or ALL(Calendar):
https://msdn.microsoft.com/en-us/library/ee634400.aspx
