Beginner needs help creating Matrix or Table Showing Previous Week Tickets vs Current Week

[Mauri_Castro_70]

Hi;

I need help creating either a table or a matrix that looks something like the example below. Where i am able to show how many tickets I had in the previous week (weekdays only / Networkdays). The total tickets during the current week, another calculation that will tell me how many tickets (using ticket id for lookupvalue) on the current week have been carried over from the previous week and finally the delta between the recurring tickes vs the current week tickets.

Weekdays only	Total Tickets Last Week	Total Tickets Current Week	Recurring Tickets (tickets in current week that were also present is last week)	New Tickets (Tickets that came in during current week)
Mon	220	275	87	188
Tue	317	291	155	136
Wed	285	313	117	196
Thu	413	367	201	166
Fri	397	425	135	290

By the way, I already have meassures that get me data for the first 3 columns (left to right); but i can't figure out how to get the meassure for the recurring tickets column data. The Delta column I am sure i can figure out (I think).

Thanks so much in advance for the help!

Mauricio

[Ashish_Mathur]

Hi,

So would it be correct to say that 220 ticket were sold on Monday of the previous week?  Do you have

1. A Date column in the Data Table; and
2. A calendar Table; and
3. A week number column in the Calendar Table

[Mauri_Castro_70]

Hi @Ashish_Mathur 

The tickets columns actually refer to "open/active" service tickets in previous and current weeks respectively. I need a calculated column or measure that gives me the total of active tickets from last week that are still open in current week (as part of the 275 tickets in current week).

To answer your questions 1, 2 and 3; the answer is yes! I have all of them.

Thanks so much for the support!

Mauricio 

[Ashish_Mathur]

Share the download link of the PBI file.

[Mauri_Castro_70]

Hi @Ashish_Mathur 

Good Day!

This is the link for you to download the sample data PBI file.

PBI Sample Files

https://1drv.ms/f/s!AsN51lqO15IAgZV_XWkFsZyJ4EOH-w?e=l9BBb8

Thanks again for all the help!

[Ashish_Mathur · Accepted solution]

Hi,

In the filter section, i have chosen the current week offset as 0.  I then wriote this measure

=Measure = CALCULATE([Total Tickets],FILTER(VALUES(Data[Ticket ID]),[Previous_Week_Tickets]>0&&[Current_Week_Tickets]>0))

I get a blank for all days.  For Friday of the current week, there are no id's that existed on the Friday of the previous week so blank is the correct answer.  I have not checked for the other days.

[Mauri_Castro_70]

Hi @Ashish_Mathur 

Thanks so much for the help!... This worked as expected. I can't thank you enough!

The reason why you were getting blanks for all days is actually beacause when I created the mocked up data, I completely forgot to include some the same Ticket ID numbers from the previous week into the current week. However; once I tried your solution in the actual data; things worked perfectly!

Thanks Again!

Regards,

Mauricio

[Ashish_Mathur]

You are welcome.

[Mauri_Castro_70]

Hi again @Ashish_Mathur 

Due to company policy I cannot share the file. I will prepare some mocked up data file and share it tomorrow. I am located in Central America and it is already late... plus I am away from my computer. 

Thanks