The Way to Insert All the Missing months and put 0 as the value

[Anonymous]

Hi everyone!

So here is my situation:  I have a BASETABLE that records the expenditure of each project in each country under each category and business unit at correspondent date of occurrence (Therefore, there are six columns in it: Date of occurrence, project, country, unit, category, expenditure) . And I need to calculate the cummulated monthly sum of expenditure based on different criteria later on and visualize the cummulated monthly sum of expenditure.

However, since not every project would generate expenditure in each month in each country, there are months that are missing in the BASETABLE. For example, if no projects generate expenditure in Vietnam in 2022-05, then there will be information at all containing any information in May (as follows). If this is the case, then it would be impossible to correctly visualize the cummulated monthly sum of expenditure in each month. The visualization will then only show the the cummulated monthly sum of expenditure in the months that appear in the BASETABLE.

date of occurrence	project	country	unit	category	expenditure
2022-04-30	xxx	Vietnam	205	3	100
2022-04-30	yyy	Vietnam	205	1	50
2022-04-30	yyy	Thailand	205	3	80
2022-06-30	xxx	Thailand	205	2	70
2022-06-30	xxx	Vietnam	205	2	120
2022-06-30	yyy	Thailand	205	1	110

I would like to ask how I could insert all the missing months and fill in the expenditure column with 0 (as follows) ? I already created a new table using MIN and MAX functions to list every day within the range of the BASETABLE based on its date of occureence row.

Thanks for the effort ahead of time!😊

date of occurrence	project	country	unit	category	expenditure
4/30/2022	xxx	Vietnam	205	3	100
4/30/2022	xxx	Vietnam	205	2	0
4/30/2022	xxx	Vietnam	205	1	0
4/30/2022	yyy	Vietnam	205	3	0
4/30/2022	yyy	Vietnam	205	2	0
4/30/2022	yyy	Vietnam	205	1	50
4/30/2022	xxx	Thailand	205	3	0
4/30/2022	xxx	Thailand	205	2	0
4/30/2022	xxx	Thailand	205	1	0
4/30/2022	yyy	Thailand	205	3	80
4/30/2022	yyy	Thailand	205	2	0
4/30/2022	yyy	Thailand	205	1	0
5/31/2022	xxx	Thailand	205	1	0
5/31/2022	xxx	Thailand	205	2	0
5/31/2022	xxx	Thailand	205	3	0
5/31/2022	xxx	Vietnam	205	1	0
5/31/2022	xxx	Vietnam	205	2	0
5/31/2022	xxx	Vietnam	205	3	0
5/31/2022	yyy	Thailand	205	1	0
5/31/2022	yyy	Thailand	205	2	0
5/31/2022	yyy	Thailand	205	3	0
5/31/2022	yyy	Vietnam	205	1	0
5/31/2022	yyy	Vietnam	205	2	0
5/31/2022	yyy	Vietnam	205	3	0
6/30/2022	xxx	Thailand	205	1	0
6/30/2022	xxx	Thailand	205	2	70
6/30/2022	xxx	Thailand	205	3	0
6/30/2022	xxx	Vietnam	205	1	0
6/30/2022	xxx	Vietnam	205	2	120
6/30/2022	xxx	Vietnam	205	3	0
6/30/2022	yyy	Thailand	205	1	110
6/30/2022	yyy	Thailand	205	2	0
6/30/2022	yyy	Thailand	205	3	0
6/30/2022	yyy	Vietnam	205	1	0
6/30/2022	yyy	Vietnam	205	2	0
6/30/2022	yyy	Vietnam	205	3	0
6/30/2022	yyy	Vietnam	205	3	0

[sjoerdvn]

Why would you want to insert extra rows? If you want to display "0" in visuals just make sure you have a proper date table and adjust your measure to show a zero where needed. 

[Anonymous]

I definitely need to insert rows because, as I have mentioned, the data I want to visualize is the cummulated monthly sum of expenditure based on country or project, not the single expenditure in expenditure column. In order to do that, I must have all the expenditure data and missing months so that I could do the calculation later on based on the complete date range.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from @sjoerdvn , please allow me to provide another insight:

Hi,@Anonymous 
Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated table references:

1 = 
VAR startdate=DATE(2022,4,1)
VAR enddate=DATE(2022,6,30)
VAR md=ADDCOLUMNS(CALENDAR(startdate,enddate),"monthend",EOMONTH([Date],0))
RETURN DISTINCT(SELECTCOLUMNS(md,"Date",[monthend]))

2 = DATATABLE("project",STRING,
"country",STRING,
"unit",INTEGER,
"category",INTEGER,
{
    {"xxx","Vietnam","205","1"},
    {"xxx","Vietnam","205","2"},
    {"xxx","Vietnam","205","3"},
    {"yyy","Vietnam","205","1"},
    {"yyy","Vietnam","205","2"},
    {"yyy","Vietnam","205","3"},
    {"xxx","Thailand","205","1"},
    {"xxx","Thailand","205","2"},
    {"xxx","Thailand","205","3"},
    {"yyy","Thailand","205","1"},
    {"yyy","Thailand","205","2"},
    {"yyy","Thailand","205","3"}
})

Table = CROSSJOIN('1','2')

3. Below are the calculated column I've created for your needs:

expenditure = IF(LOOKUPVALUE('blank'[expenditure],
'blank'[category],'Table'[category],
'blank'[country],'Table'[country],
'blank'[date of occurrence ],'Table'[Date],
'blank'[project],'Table'[project],
'blank'[unit ],'Table'[unit]),
LOOKUPVALUE('blank'[expenditure],
'blank'[category],'Table'[category],
'blank'[country],'Table'[country],
'blank'[date of occurrence ],'Table'[Date],
'blank'[project],'Table'[project],
'blank'[unit ],'Table'[unit]),0)

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.