Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MdJ83
Helper II
Helper II

Create a new table summarizing the total for the each month

Hi 

 

I want to create a new table that summarises the totals for each prodcut see the two table below input and output

 

DateProductAmount
1/01/2021A100
2/01/2021B200
3/01/2021C100
4/01/2021A200
5/01/2021B300
5/02/2021A200
6/02/2021B100
7/02/2021C50
8/02/2021B100
   
MonthProductDate
Jan-21A300
Jan-21B500
Jan-21C100
Feb-21A200
Feb-21B200
Feb-21C50
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@MdJ83 , You can add a month Year column in Power Query

Date.ToText([Date], "MMM-yyyy")

 

And then use group by https://docs.microsoft.com/en-us/power-query/group-by

 

OR

 

Dax add  a column

= format([Date], "mmm-yyyy")

 

Then use summarize(Table, Table[Month Year], "Total", SUm(Table[Amount]) )

 

or use on visual with month year and sum of amount

View solution in original post

tamerj1
Super User
Super User

HI @MdJ83 
You can try the following code

Products Monthly Summary =
VAR T1 =
    ADDCOLUMNS (
        Products,
        "Month",
            FORMAT ( DATE ( 1, MONTH ( Products[Date] ), 1 ), "MMM" ) & "-"
                & RIGHT ( YEAR ( Products[Date] ), 2 )
    )
RETURN
    SUMMARIZE (
        T1,
        [Month],
        Products[Product],
        "Total Amount", SUM ( Products[Amount] )
    )

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

HI @MdJ83 
You can try the following code

Products Monthly Summary =
VAR T1 =
    ADDCOLUMNS (
        Products,
        "Month",
            FORMAT ( DATE ( 1, MONTH ( Products[Date] ), 1 ), "MMM" ) & "-"
                & RIGHT ( YEAR ( Products[Date] ), 2 )
    )
RETURN
    SUMMARIZE (
        T1,
        [Month],
        Products[Product],
        "Total Amount", SUM ( Products[Amount] )
    )
amitchandak
Super User
Super User

@MdJ83 , You can add a month Year column in Power Query

Date.ToText([Date], "MMM-yyyy")

 

And then use group by https://docs.microsoft.com/en-us/power-query/group-by

 

OR

 

Dax add  a column

= format([Date], "mmm-yyyy")

 

Then use summarize(Table, Table[Month Year], "Total", SUm(Table[Amount]) )

 

or use on visual with month year and sum of amount

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors