topic Aggregate daily sales data to monthly and each month data should be displayed in separate column in DAX Commands and Tips

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

Simran-Fathima — Fri, 15 Jul 2022 11:20:37 GMT

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

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

amitchandak — Fri, 15 Jul 2022 11:36:48 GMT

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

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

Jihwan_Kim — Fri, 15 Jul 2022 11:44:55 GMT

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.

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

Simran-Fathima — Mon, 18 Jul 2022 05:56:39 GMT

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?

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

Jihwan_Kim — Mon, 18 Jul 2022 09:00:18 GMT

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

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

Simran-Fathima — Mon, 18 Jul 2022 11:43:14 GMT

Didnt get the desired output. the output i got:

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

Simran-Fathima — Mon, 18 Jul 2022 11:52:43 GMT

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.

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

Jihwan_Kim — Mon, 18 Jul 2022 17:03:29 GMT

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.

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

Simran-Fathima — Mon, 18 Jul 2022 17:47:29 GMT

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_3JCjWlmK7xPiOXwpxoFQ

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

Jihwan_Kim — Mon, 18 Jul 2022 18:07:23 GMT

Hi,

I do not have an access.

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

Simran-Fathima — Mon, 18 Jul 2022 18:11:04 GMT

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

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

Simran-Fathima — Mon, 18 Jul 2022 18:27:44 GMT

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