How to Aggregate months from one column and total sales from another column

[Antonio195754]

Hello i have a dataset like the one below, and i'm trying to aggregate the months and respective sales, as well as formatting the months, as seen in the second table.  I can do this with reference tables or group function, but i'm trying to do this with DAX.

Can someone please help me with the DAX?  Thank you

Month	Sales
July 01 2003	4
July 02 2003	5
July 03 2003	8
August 07 2003	11
August 09 2003	6
August 12 2003	9
September 07 2003	8
September 10 2003	5

Month	Sales
Jul 03	17
Aug 03	26
Sep 03	13

[danextian · Accepted solution]

Hi @Antonio195754 ,

The approach depends on whether your month column is actually a date or a text.
If a date:

Month2 = 
FORMAT ( 'Table'[Month], "mmm yy" )

Sort =
FORMAT ( 'Table'[Month], "yyyymm" )

If a text:

Month2 =
FORMAT ( DATEVALUE ( 'Table'[Month2] ), "mmm yy" )

Sort =
FORMAT ( DATEVALUE ( 'Table'[Month2] ), "yyyymm" )

Since either of those formula will return a text string, the values will be sorted alphabetically thus the need for a custom column sort.

[Antonio195754]

That was so simple.  It worked like a charm.  Created both columns and then sorted off the sort column, aggregated my months with the sales rolling up to it.  Thank you!

[Antonio195754]

The date field was in date format, so went with your first suggestion.

[Ashish_Mathur]

Hi,

Ensure that the Month column has proper right aligned dates.  Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag Year and Month name from the Calendar Table.  Write this measure

Total = sum(Data[Sales])

Hope this helps.

[Antonio195754]

Hey @Ashish_Mathur yes i did it that way originally and it worked, but to improve my DAX skills, i'm trying to accomplish it that way.  Any suggestions?  The date field comes in as date format.