Forum Discussion
Fill missing month row with zero
I have 2 tables, 'All months table' and 'Events table'. I would like to get a new table (Result Table) in which the events are summarized and the missing months are filled with zero. Does anyone knows how I can do this?
Hi Ralf123,
First of all, I would model your data in your Events table differently, like this:
Events table EventName Date Event X 01/02/2017 Event X 02/02/2017 Event X 01/03/2017 Event X 01/04/2017 Event X 02/04/2017 Event X 01/05/2017 Event X 01/06/2017 Event Y 01/01/2017 etc… I used a "Date" column because you have more than one instance of the same event per month.
Then, I would create a Calendar table, rather than an All Months table, e.g.:
Calendar = ADDCOLUMNS ( CALENDAR ( "01-Jan-2010", "31-Dec-2020" ), "Year", YEAR ( [Date] ), "Month", FORMAT ( [Date], "MM" ), "Month Name", FORMAT ( [Date], "MMM" ) )Then create a relationship between the two tables using the [Date] column.
Then you should be able to create a Matrix visual that resembles your Result Table.
Let me know how you get on.
2 Replies
Hi Ralf123,
First of all, I would model your data in your Events table differently, like this:
Events table EventName Date Event X 01/02/2017 Event X 02/02/2017 Event X 01/03/2017 Event X 01/04/2017 Event X 02/04/2017 Event X 01/05/2017 Event X 01/06/2017 Event Y 01/01/2017 etc… I used a "Date" column because you have more than one instance of the same event per month.
Then, I would create a Calendar table, rather than an All Months table, e.g.:
Calendar = ADDCOLUMNS ( CALENDAR ( "01-Jan-2010", "31-Dec-2020" ), "Year", YEAR ( [Date] ), "Month", FORMAT ( [Date], "MM" ), "Month Name", FORMAT ( [Date], "MMM" ) )Then create a relationship between the two tables using the [Date] column.
Then you should be able to create a Matrix visual that resembles your Result Table.
Let me know how you get on.
Great, this did the trick.
You saved me a lot of time, thanks
