cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alex_Hasan
Helper II
Helper II

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

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? 

2 ACCEPTED SOLUTIONS
tamerj1
Community Champion
Community Champion

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]
)

View solution in original post

daXtreme
Solution Specialist
Solution Specialist

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.

View solution in original post

18 REPLIES 18
tamerj1
Community Champion
Community Champion

hi @Alex_Hasan 

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

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? 

tamerj1
Community Champion
Community Champion

@Alex_Hasan 

How does your visual look like?

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

tamerj1
Community Champion
Community Champion

@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?

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"
)
)
tamerj1
Community Champion
Community Champion

@Alex_Hasan 
Here is the code with clarification

 

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

or

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

 

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.

tamerj1
Community Champion
Community Champion

can share a screeshot?

 

This is the maximum sales of january 2011. 

Capture1.JPG

 

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

Capture2.JPG

daXtreme
Solution Specialist
Solution Specialist

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.

tamerj1
Community Champion
Community Champion

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

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

By date. 

tamerj1
Community Champion
Community Champion

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]
)
daXtreme
Solution Specialist
Solution Specialist

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.

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? 

tamerj1
Community Champion
Community Champion

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors