Compare daily expected task (by area, location type, location) with actual

[HoreaL]

Hi,

I have a table with expected tasks executed daily by: area, location type and location.

Something like this:

Day	Area	Location type	Location	Task
Monday	Area1	House1	Room1	Clean
Monday	Area1	House1	Room2	Wash
Monday	Area1	House2	Room1	Fix
Monday	Area2	House3	Room1	Wash
Monday	Area2	House3	Room2	Fix
Monday	Area3	House4	Room1	Clean
Monday	Area3	House4	Room2	Wash
Monday	Area3	House5	Room1	Fix
Tuesday	Area1	House1	Room2	Clean
Tuesday	Area1	House2	Room1	Fix
Tuesday	Area2	House1	Room1	Wash
Tuesday	Area3	House1	Room2	Wash
Tuesday	Area3	House2	Room1	Fix
…	…	…	…	…

Here I have Day (Monday, Tuesday, ...) but not date.

And then I have in another table with daily tasks completed. 

Day	Data	Area	Location type	Location	Task
Monday	11/04/2022	Area1	House2	Room1	Clean
Monday	11/04/2022	Area2	House3	Room1	Wash
Monday	11/04/2022	Area2	House3	Room2	Fix
Monday	11/04/2022	Area3	House4	Room2	Wash
Monday	11/04/2022	Area3	House5	Room1	Fix
Tuesday	12/01/1900	Area1	House2	Room1	Fix
Tuesday	12/01/1900	Area3	House1	Room2	Wash
…		…	…	…	…

I want to make a (column or line) chart where I can see expected tasks versus actual tasks done by day (plus and another chart by week, and another chart my month).

[amitchandak · Accepted solution]

@HoreaL , On way is to create a concatenated column both table for these

Day

Area

Location type

Location

And join and dimension, will have expected task and table will have actual task and you can display that in matrix 

or bring a new column in Table 2

maxx(filter(Table1, Table1[Day] = Table2[Day] && Table1[Area] = Table2[Area]&& Table1[Location type] = Table2[Location type] && Table1[Location] =Table2[Location]), Table1[Task])

now again both Task on matrix

[HoreaL]

Hi @amitchandak,

Thank you for your help, but...

If I make a concatenated column on each table, I will have:

- on Table1 (expected tasks) data for only one week (from monday till sunday)

- on Table2 (actual tasks) data from monday till sunday but for 52 weeks (one year). 

It will be: Table1 = 7, Table2 = 7x52. 

The easy way would be to compare Table1 (expected tasks in 1 week) with Table2 (actual tasks in a week from 52 weeks of a year).

The long way would be to make in Table1 (expected tasks) tasks for all weeks (monday - sunday x 52 weeks) and then compare them (1:1) with tasks from Table2 (actual tasks)... but I would prefer something simpler, not so complicated.