cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bmurfy Frequent Visitor
Frequent Visitor

Display Date using Time Intelligence

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.


Video1 =
var flgvideo = VALUES('events'[VideoFlag])
var eventdate = LASTDATE('events'[Date])
var firstvideo = MIN('Date'[Calendar WeekNumber]) -4
return

IF(flgvideo=TRUE(),
     CALCULATE( MIN('Date'[Date]),
                'Date'[Calendar YearNumber]=YEAR(eventdate),
                'Date'[Calendar WeekNumber]= firstvideo,
                'Date'[Week Day] = "Mon"
       ),
      "No Video"
)
 
 
MY RESULTS (in  a table visualization):
When flgvideo is FALSE, I get "No Video"
When flgvideo is TRUE, I get an empty field!    
I think its because I'm not filtering down to a single record in the 'DATE' table...but I'm not sure.
 
Thanks for your help!
 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Display Date using Time Intelligence

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:

 

1.JPG

 

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team
Community Support Team

Re: Display Date using Time Intelligence

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bmurfy Frequent Visitor
Frequent Visitor

Re: Display Date using Time Intelligence

Community Support Team
Community Support Team

Re: Display Date using Time Intelligence

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:

 

1.JPG

 

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bmurfy Frequent Visitor
Frequent Visitor

Re: Display Date using Time Intelligence

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!

Community Support Team
Community Support Team

Re: Display Date using Time Intelligence

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.