Create table sum value by month

[Anonymous]

Hello everyone,

I need help to create this table :

I try some tips but I cannot do it..

Name	sum January	sum February	sum March	sum April	sum May	sum June	sum July	sum August	sum September	sum October	sum November	sum December	Total SUM
Toto	21	323	32	54	65	65	76	78	45	45	43	32

I have this two columns in my original table :

DATE	VALUE toto
14/04/2020 00:00	62
14/04/2020 01:00	61,5
14/04/2020 02:00	62
14/04/2020 03:00	60,167
15/04/2020 00:00	58,333
15/04/2020 01:00	58
15/04/2020 02:00	59
15/04/2020 03:00	59,833
16/04/2020 00:00	58,667
16/04/2020 01:00	59
16/04/2020 02:00	57
16/04/2020 03:00	69,167

Can you help my ?

Thank you

[Anonymous]

Thank you @amitchandak

I am not sure to be clear.

This is my input data :

PLOT DATE	VALUE
14/04/2020 00:00	62
14/04/2020 01:00	61,5
14/04/2020 02:00	62
14/04/2020 03:00	60,167
15/04/2020 00:00	58,333
15/04/2020 01:00	58
15/04/2020 02:00	59
15/04/2020 03:00	59,833
16/04/2020 00:00	58,667
16/04/2020 01:00	59
16/04/2020 02:00	57
16/04/2020 03:00	69,167

I would like to create this table in power BI:

	January	February	March	April	May	June	July	Total SUM
VALUE	0	0	0	724,667	0	0	0	724,667

[Anonymous]

I obtain this table :

 

But I would like to invert Columns and rows.

Like month on first rows and value name on first columns.

Thank you in advance

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may put Month level of [PLOT DATE] date hierarchy into Columns box of Matrix visual, put [Value name] into Rows box, put [Value] into Values box , set aggregation as "Sum".

Best Regards,

Amy 

Community Support Team _ Amy

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

[amitchandak]

@Anonymous , Not very clear to me. But you can create a date table and join it with date and have month year in date table and group data on that.

In case of date has a timestamp , create a new date to join

New Date =[date].date

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.