GroupBy function to include zero values

[Anonymous]

Hi,

I am looking to group my data of InvoicedQty by Yearmonth. I need the grouped sum data to include zero values.
I have data in the following format:

YearMonth	InvoicedQty
2019/01	100
2019/02	25
2019/02	35
2019/02	32
2019/02	14
2019/05	52
2019/05	13
2019/06	52

I would need the outcome to look like this:

YearMonth	InvoicedQty
2019/01	100
2019/02	106
2019/03	0
2019/04	0
2019/05	65
2019/06	52

I have succeeded in grouping the data but seem to not be able to make the YearMonths with zero invoicedQty to not dissappear. Any tips?

-Jesper

[Anonymous]

Hi @Dinesh_Suranga ,

This does work, it leaves the value blank. My apologies, my purpose did not become clear earlier. I need to then use the dataset to create a r visual. want to create a forecast. The dataset I need for the R visual needs to contain a 0 for the months with no InvoicedQty, not blank. To my knowledge this does not fix this issue.
Thank You.

[Dinesh_Suranga]

Hi @Anonymous ,

Do you use a separate calendar table?  

Thank you.

[Anonymous]

Hi @Dinesh_Suranga ,

I have a separate table containing all possible YearMonth values but am having trouble connecting it to the group function. Did you have some idea in mind?
Thank you.

[Dinesh_Suranga]

Hi @Anonymous ,

Do you want to create this in the power query side?

Thank you

[Anonymous]

Hi @Dinesh_Suranga 
Perhaps rather as DAX, but which ever way works best.
Thank you

[Dinesh_Suranga]

Hi @Anonymous,

Please try this first.

Right click on YearMonth tick show item with no data.

If it does not work, will go to the next step.

Thank you.