Data Structure

[Anonymous]

I have a targets table for 3 offices for the current month and the format is as follows:

(The date column is for each day of the month, Office ID contains the ID of the office, Target is the target for the day)

Office ID | Date (day) | Target 

This pulls in from a database but only gives me rows up to the current date, for example, if I load the data today the date column runs per day from the 1st of March to the 26th of March. I would like to graph a burn-up chart so ideally I would like to add additional rows to the bottom of the table for EACH office ID for the remaining days in the month (27th, 28th, 29th, 30th, 31st) and calculating their remaining target in the same column, this way I can put it into a graph. 

My question is to understand the best way to do this and how.

Thanks

[v-yulgu-msft]

Hi @Anonymous ,

New a date dimension table.

datedime = CALENDAR(DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0))

Establish a one to many relationship between two tables.

Then, you can drag date field from date dimension onto chart axis, and add value fields or calcualted measures into chart. However, I cannot understand how to "calculating their remaining target in the same column"? Please illustrate with some sample data.

Best regards,

Yuliana Gu

[Anonymous]

Thanks, Yuliana.

I've created my ideal output in Excel (I should have started with this, apologies). This will also have an Office ID column so I can view one office at a time. Target achieved is calculated from some revenue data from a database and then the remaining days is calculated as the (total target for the month - total achieved) / days remaining. This is for every value from the 27th to the 31st currently as below. Base target is the total target for the month / total days in the month: