Aggregate daily sales data to monthly and each month data should be displayed in separate column

[Simran-Fathima]

I have different products sold on every day. I want to know in a particular month how many pieces of a particular product were sold.

The data is :

And the Output should be :

I request for the response as soon as possible. Please anyone help me

[amitchandak]

@Simran-Fathima , Create a date table and join with your date and Month year there

Try a measure like sum(Table[No of sales]) +0

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I created a calendar table with having month name column and month number column. This helps to create a monthly aggregated information.

[Simran-Fathima]

Yes this is what I required. But I dont want blank spaces for the month which dont have sales. Instead it should be filled with "0". Can u please help me out on how to do this?

[Jihwan_Kim]

Hi,

Thank you for your message.

Please try to write measure (explicit measure) like below and put it into the visualization.

Please check the below measure and the attached file.

No of sales measure: = 
SUM( Data[No of sales] ) + 0

[Simran-Fathima]

Didnt get the desired output. the output i got:

[Simran-Fathima]

I'm not sure if I'm doing any mistake related to managing permissions. I'll send the screenshot of the relationships. Let me know where did i go wrong. 

I'm a beginner to power bi. So I seek for a help.

[Jihwan_Kim]

Hi,

Please share your pbix file's link (onedrive or googledrive or dropbox...), and then I can try to have a look into it.

Thank you.

[Simran-Fathima]

I have shared you the link of that pbix file Link. Hope a solution for my problem

https://my.shell.com/:u:/g/personal/simran_fathima_shell_com/EeJgI9NmQsZHqKLD2JB7iAcBm_3JCjWlmK7xPiO... 

[Jihwan_Kim]

Hi,

I do not have an access.

[Simran-Fathima]

I'm not aware of how to share pbix file directly here

[Simran-Fathima]

I got the solution as expected. Thank you so much for your help