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.
I'm trying to use Time Intelligence to calculate the specific date to show a video based on the date of an event.
I have 2 tables:
- a simple Azure SQL table - 'event', that contains a boolean field [videoflag]
- A full function calculated 'DATE' table (from SQLBI)
and a slicer that filters the 'event' table for a specific event by name.
If the [videoflag] for that event is TRUE, then the [video1] measure should display the date of the Monday, four weeks prior to the event date.
Solved! Go to Solution.
hi, @bmurfy
Adjust your formula as below:
In "event" table, add a Calendar WeekNumber column from calendar table
Calendar WeekNumber = RELATED('Date'[Calendar WeekNumber])
Then use this formula
Video1 = var flgvideo = VALUES('event'[VideoFlag]) var eventdate = LASTDATE('Date'[Date]) var firstvideo = MIN(event[Calendar WeekNumber]) -4 return IF(flgvideo=TRUE(), CALCULATE( MIN('Date'[Date]),FILTER(ALL('Date'), 'Date'[Calendar YearNumber]=YEAR(eventdate)&& 'Date'[Calendar WeekNumber]= firstvideo&& 'Date'[Week Day] = "Mon") ), "No Video" )
Result:
Best Regards,
Lin
hi, @bmurfy
Sampe pbix file or Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Lin
hi, @bmurfy
Adjust your formula as below:
In "event" table, add a Calendar WeekNumber column from calendar table
Calendar WeekNumber = RELATED('Date'[Calendar WeekNumber])
Then use this formula
Video1 = var flgvideo = VALUES('event'[VideoFlag]) var eventdate = LASTDATE('Date'[Date]) var firstvideo = MIN(event[Calendar WeekNumber]) -4 return IF(flgvideo=TRUE(), CALCULATE( MIN('Date'[Date]),FILTER(ALL('Date'), 'Date'[Calendar YearNumber]=YEAR(eventdate)&& 'Date'[Calendar WeekNumber]= firstvideo&& 'Date'[Week Day] = "Mon") ), "No Video" )
Result:
Best Regards,
Lin
Perfect, Thanks!
Just for clarity...
a) was the week # column necessary in the event table to eliminate multiple rows returning in the results?
b) Why was the FILTER necessary?
c) What was your formula for the Videoflag measure?
d) wondering how you got all the event rows to show up? When I de-select the slicer I get an error with [video1]
"A table of multiple values was supplied where a single value was expected"
Thanks again!
hi, @bmurfy
1. If week # column not in the event table, there are many week # column in calendar table, it will return every week # for 2019.
2. FILTER is not necessary
Best Regards,
Lin
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 |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |