Filtering Rows in Table Based on a Dynamic Date

[simbobaker]

Hi All,

I'm relatively new to Power BI and am part-way through a project in-which I am building Reports using ticket data from our Helpdesk System.

My Data looks like this:

'Tickets' Table containing the following columns (only relevant columns have been added so far):

Columns: ticketid [INT], isresolved [BINARY], datecreated [DATE], dateresolved[DATE]

Example: 10958, 1, 02-08-2016, 06-08-2016

'Calendar' Table containing a load of dates with the following columns:

Date[DATE],ThisWeek[BINARY],WeekDay[INT]

I am building a particular report that gets 'ThisWeeks' activity.

So...

How many tickets were Created in the week?

How many tickets were Resolved in the week?

How many tickets were Carried over from the previous week?

The last question is where I'm getting stuck. At the moment I am attempting to filter the 'Tickets' table to find only the tickets that were created prior to the first day of the week AND were closed on/after the first day of the week OR is not resolved.

If I was writing this as a MySQL Query for example it would be:

SET @a = SELECT date FROM calendar WHERE ThisWeek='1' AND DayofWeek='0'

SELECT * FROM tickets WHERE datecreated=<@a AND (isresolved='0' OR dateresolved=>@a)

Anyone got any ideas on how it could be achieved in Power BI?

Thanks in Advance.

[Anonymous · Accepted solution]

Hi @simbobaker,

Based on your description, you want to convert the mysql query to dax query, right?
If as I said, you can use below formula if it works on your side:

Tickets table:
 

Date table:
DateTable = ADDCOLUMNS(CALENDAR(date(2015,1,1),TODAY()),"ThisWeek",if(WEEKNUM([Date])=WEEKNUM(NOW()),1,0),"DayofWeek",WEEKDAY([Date],3))

Table =
var temp=MAXX(FILTER(DateTable,DateTable[ThisWeek]=1&&DateTable[DayofWeek]=0),[Date])
return
FILTER(ALL(Tickets),AND(Tickets[datecreated]<=temp,OR(Tickets[isresolved]=0,Tickets[dateresolved]>=temp)))

Regards,
Xiaoxin Sheng

[simbobaker]

Thank you very much @Anonymous! This solution worked perfectly.

Many Thanks,

Simon