Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have added a date table to my data model but can't get the dates to flow through to my visuals when a value from another table is added to the visual.
I define the date table using DAX with the following formula:
Date =
ADDCOLUMNS(
CALENDAR(DATE(2015,1,1), DATE(2030,12,31)),
"DateAsInteger", FORMAT([Date], "YYYYMMDD"),
"Year", YEAR([Date]),
"Quarter", "Q" & if(QUARTER([Date]) >= 3, QUARTER([Date]) - 2, QUARTER([Date]) + 2),
"Monthnumber", FORMAT([Date], "MM"),
"YearMonthnumber", FORMAT([Date], "YYYY/MM"),
"YearMonthShort", FORMAT([Date], "YYYY/mmm"),
"MonthNameShort", FORMAT([Date], "mmm"),
"MonthNameLong", FORMAT([Date], "mmmm"),
"WeekNumber", WEEKNUM([Date], 2),
"DayOfWeekNumber", WEEKDAY([Date]),
"DayOfWeek", FORMAT([Date], "dddd"),
"DayOfWeekShort", FORMAT([Date], "ddd")
)
I then mark the date table as such using the 'Date' column. It validates without any issues.
In my data model I insert a many-to-one relationship from a fact table to the date table from field 'ListDailyMeasures'[Date] to 'Date'[Date] as per the screen shot below:
(There are obviously some other relationships between the date table and other tables that I am not covering for brevity).
When I add the date field from my date column into a visual, all the dates display as expected.
However, as soon as I add a field from the related table (in this example, 'ListDailyMeasures'), I lose the dates:
For context, this is what the data in the 'ListDailyMeasures' table looks like - the value field is just an integer recorded against a date (I have filtered out the rows where Measure = 'Currently Isolating' in the visual which is why there are only two values):
If I instead use the Date field from the 'ListDailyMeasures' table, the dates do display:
However, my understanding from what I've read online is that I should be using the Date field from the Date table in my visuals.
Any advice on where I'm going astray much appreciated.
Solved! Go to Solution.
@LL_Yoor , Please change the data type of date to datetime and use format to show time and check there should not be any time other then 12 AM, if there is time other than 12 AM, then create a new date column and join on that
New Date = datevalue([Date])
Second date table may be missing dates which are there in date column of your tables.
please other reasons
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
@LL_Yoor , Please change the data type of date to datetime and use format to show time and check there should not be any time other then 12 AM, if there is time other than 12 AM, then create a new date column and join on that
New Date = datevalue([Date])
Second date table may be missing dates which are there in date column of your tables.
please other reasons
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Thanks @amitchandak - it was a timestamp issue. I had converted the data type of the date in my fact table to 'Date' but that wasn't working - had to follow your conversion tip in the video.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |