Daily Variance - 2 datasets / worksheets

[Newbie_77]

Hi All,

My boss has requested a table/matrix to represent daily changes that occur within our booking system.

I have added the sheet to GDrive, with link below.   This contains the actual sheet that I receive from the system, with cut down number of rows.  I need to add to my reporting model a simple table or matrix that shows the output as shown in the sheet.  I have kept all of the headings in the table as I will need these for other visualisations.  This specific requirement just needs the 'Description' and then a 'Qty' for each of those activities listed across two days, then a difference/variance column adding  (dataset currently is 5000 rows, with around 20 different Descritions.

The data extracted is run across the same data with additional latest date added each day.   The aim is to track changes that are made to bookings across a previous date range and all future data.  Data is extracted for 3 months into future.  

So on 12th June if we extract all data, it may show there are 9 bookings for 'Training' in the month of June.  If running the same extract for 13th June (next day), the report may show there are 8 bookings for training for month of June (one booking had a status change).  This will inform the user that there was a change to a future booking (nothing can change in the past), with one training day removed from the diary.   

https://drive.google.com/drive/folders/1HQmANpuSYg25ptWc2utDZWNyVuxaTkBt?usp=sharing

I would be very grateful if anyone has time to look into this for me.

Many thanks,]Phil.

[v-danhe-msft · Accepted solution]

Hi @Newbie_77,

Based on my test, you can refer to below steps:

1.Create a new table to union your [DESCROPTION] in Table ‘Day1’ and Table ‘Day2’>. Create three measures to count the [DESCRIPTION] in Table’Day1’ and the Table’Day2’ and the difference.

Table = DISTINCT(UNION(VALUES(Day1[DESCRIPTION]),VALUES(Day2[DESCRIPTION])))

Day1 = IF(ISBLANK(COUNTROWS(FILTER(Day1,HASONEVALUE(Day1[DESCRIPTION])))),0,COUNTROWS(FILTER(Day1,HASONEVALUE(Day1[DESCRIPTION]))))

Day2 = IF(ISBLANK(COUNTROWS(FILTER(Day2,HASONEVALUE(Day2[DESCRIPTION])))),0,COUNTROWS(FILTER(Day2,HASONEVALUE(Day2[DESCRIPTION]))))

Difference = [Day1]-[Day2]

2.

2.Create  two relationships to link the three tables.

3.Create a Table visual and add the ‘Table1’[DESCRIPTION], ‘Day1’[Day1], ‘Day2’[Day2] and [Difference] fields. And now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/a9hl5n7udx2nvmk/Daily%20Variance%20-%202%20datasets%20%20worksheets.pbix?d...

Regards,

Daniel He

[Newbie_77]

Hi Daniel,

Many many thanks, exactly what i needed.  

I love this community, extremely helpful.

Regards,

Phil.

[v-danhe-msft]

Hi @Newbie_77,

It's pleasant that your problem has been solved, could you please mark my reply as Answered?

Regards,

Daniel He