Display Date using Time Intelligence

[bmurfy]

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!

[v-lili6-msft]

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

[bmurfy]

link to pbix file...

https://www.dropbox.com/sh/lix8v5kd5lizyi0/AABSh0evKsknzw09MhLpxLZ1a?dl=0

[v-lili6-msft · Accepted 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

[bmurfy]

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!

[v-lili6-msft]

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