Not able to summarize date column to month wise to create table with summary of sales

[rajasuresh]

Hi,

Thanks for reading my question and coming forward to help me.

Objective: Creation of Graph which shows Month wise Average Per Order Sales Recived. It is good if I create table using master table just by adding additional columns or it is creating a new table with the master table.

Sample Date: Order No is unique, N number orders are delivered per Day (YYYY-MM-DD)

Master Table:

OrderNo       DeliveredDate      GrandTotal

1234              2022-01-01          450

5678              2022-01-01          500

5679              2022-01-01          650

5680              2022-02-01          500

5681              2022-02-01          550

5678              2022-03-01          500

I can share excel sheet with the data if required.

Thanks

Suresh

[rajasuresh]

Hi Liu Yang,

First of all thank you very much for responding to my problem, my sincerie apologies that I missed one important point i.e., date will have multiple years too i.e., 

OrderNo       DeliveredDate      GrandTotal

1234              2022-01-01          450

5678              2022-01-01          500

5679              2022-01-01          650

5680              2022-02-01          500

5681              2022-02-01          550

5678              2022-03-01          500

5679              2023-01-01          450

5680              2023-01-01          500

5681              2023-02-01          650

5682              2023-02-01          500

5683              2023-02-01          550

5684              2023-03-01          500

Thanks

Suresh

[Anonymous]

Hi @rajasuresh ,

Here are the steps you can follow：

1. Create calculated column.

Month = MONTH('Table'[DeliveredDate])

2. Create calculated table.

Table 2 =
SUMMARIZE(
'Table','Table'[Month],"Avg",AVERAGEX(FILTER(ALL('Table'),'Table'[Month]=EARLIER('Table'[Month])),[GrandTotal]))

3. Result:

Best Regards,

Liu Yang

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