topic Re: Maximum sales of a of a specific month within a specific year in DAX Commands and Tips

Maximum sales of a of a specific month within a specific year

Alex_Hasan — Thu, 19 May 2022 17:45:38 GMT

I have created a measre named Total Sales. In the Dimdate table, there are Month, and Calender Year columns. Now I want to write a measure, Maximum sales of January 2011. I tried to solve it many ways but nothing works as I cannot organize the algorithm to solve it. How to do that?

Re: Maximum sales of a of a specific month within a specific year

tamerj1 — Thu, 19 May 2022 18:09:09 GMT

Alex_Hasan

How does your visual look like?

Re: Maximum sales of a of a specific month within a specific year

Alex_Hasan — Fri, 20 May 2022 05:10:13 GMT

The function is no working. I tried with MAX, MAXX, and Calculate but getting red underline.

Re: Maximum sales of a of a specific month within a specific year

tamerj1 — Fri, 20 May 2022 06:05:39 GMT

Alex_Hasan

I mean what are you slicing by? What visual are you using? Table, matrix, chart or just a card visual? What columns are involved in that visual? What exactly is the code that you have used but recieved error?

Re: Maximum sales of a of a specific month within a specific year

Alex_Hasan — Fri, 20 May 2022 12:45:33 GMT

Oh, okay. It's for card visual. This is how I tried.

MAX sales JAN 2011 =

MAX(CALCULATE([Total Sales],

DimDate[CalendarYear] = 2011 || DimDate[Month] = "January"

)

Re: Maximum sales of a of a specific month within a specific year

tamerj1 — Fri, 20 May 2022 12:51:46 GMT

hi Alex_Hasan

Maximum sales of January 2011 = MAXX ( CALCULATETABLE ( Sales, DimTable[Month] = 1, DimTable[Year] = 2011 ), Sales[Sales] )

Re: Maximum sales of a of a specific month within a specific year

Alex_Hasan — Fri, 20 May 2022 13:27:03 GMT

Hi, I have a measure Total sales. So calculatetable woun't take a measure. Could you please give me another solution that take a measure?

Re: Maximum sales of a of a specific month within a specific year

tamerj1 — Fri, 20 May 2022 13:35:42 GMT

This is not the code I provided. Please check with the same code

Re: Maximum sales of a of a specific month within a specific year

tamerj1 — Fri, 20 May 2022 13:41:28 GMT

Alex_Hasan
Here is the code with clarification

Maximum sales of January 2011 = MAXX ( CALCULATETABLE ( SalesTableName, DimTable[Month] = "January", DimTable[CalendarYear] = 2011 ), SalesTableName[SalesAmountColumnName] )

Maximum sales of January 2011 = MAXX ( CALCULATETABLE ( SalesTableName, DimTable[Month] = "January", DimTable[CalendarYear] = 2011 ), [Total Sales] )

Re: Maximum sales of a of a specific month within a specific year

Alex_Hasan — Fri, 20 May 2022 13:55:49 GMT

It's not showing correct value. And I repeat, I must use Total sales measure instead of sales table, not a single column from sales table.

Re: Maximum sales of a of a specific month within a specific year

tamerj1 — Fri, 20 May 2022 13:56:44 GMT

can share a screeshot?

Re: Maximum sales of a of a specific month within a specific year

Alex_Hasan — Fri, 20 May 2022 14:04:01 GMT

This is the maximum sales of january 2011.

When I tried with your code, this is what I got.

Re: Maximum sales of a of a specific month within a specific year

tamerj1 — Fri, 20 May 2022 14:09:21 GMT

What is the definition of maximum sales? By product, by customer, by date, by invoice number?

Re: Maximum sales of a of a specific month within a specific year

Alex_Hasan — Fri, 20 May 2022 14:12:26 GMT

By date.

Re: Maximum sales of a of a specific month within a specific year

tamerj1 — Fri, 20 May 2022 14:26:27 GMT

This shalk give you the sales amount durinf Jan 2011 at the date that scored the highest sales

Maximum sales of January 2011 = MAXX ( SUMMARIZE ( CALCULATETABLE ( SalesTableName, DimDate[Month] = "January", DimDate[CalendarYear] = 2011 ), DimDate[Date], "@Amount", [Total Sales] ), [@Amount] )

Re: Maximum sales of a of a specific month within a specific year

Alex_Hasan — Fri, 20 May 2022 14:29:21 GMT

This is the link. you will find the excercise in context transition page
https://1drv.ms/u/s!AtNgJBWI0wXOb5GOZf-anQJd3dc?e=6dgu3t

Re: Maximum sales of a of a specific month within a specific year

daXtreme — Fri, 20 May 2022 14:30:04 GMT

To be able to write the code you want one has to know the organization of your Sales table. Is every row a complete sale? If now, then which column identifies all the rows that belong to the same sale? You have to answer such question first to be able to write the desired formula.

Re: Maximum sales of a of a specific month within a specific year

Alex_Hasan — Fri, 20 May 2022 14:54:39 GMT

It worked ! Thank you. But I would like know why we used SUMMERIZE function? and why "@Amount" in that way? could you please elaborate, it seems a tough one?

Re: Maximum sales of a of a specific month within a specific year

daXtreme — Fri, 20 May 2022 15:09:15 GMT

One of them will be the fastest:

Maximum sales of January 2011 = MAXX ( SUMMARIZE ( CALCULATETABLE ( SalesTableName, DimDate[Month] = "January", DimDate[CalendarYear] = 2011 ), DimDate[Date] ), [Total Sales] ) -- or Maximum sales of January 2011 = CALCULATE( MAXX ( SUMMARIZE ( SalesTableName, DimDate[Date] ), [Total Sales] ), DimDate[Month] = "January", DimDate[CalendarYear] = 2011 ) -- or Maximum sales of January 2011 = CALCULATE( MAXX ( // on condition that SalesTableName[Date] // is connected to your DimDate table // on Date. VALUES ( SalesTableName[Date] ), [Total Sales] ), DimDate[Month] = "January", DimDate[CalendarYear] = 2011 )

By the way, SUMMARIZE should never be used for calculations, only for grouping, since this function has a bug that Microsoft have refused to fix. Alberto Ferrari has an article about it.