exact date of max of sum per day among the month

[Thoms]

Dear friends,

Ran into an issue,

need to add visual column (red one) with date when was max in the month. is it possible? 

max in month by product = 

Measure 2 = var _table=CALCULATETABLE(SUMMARIZE('Table','Table'[city],'Table'[product #],'Table'[Month],'Table'[date],"_pcs",SUM('Table'[pcs])),ALL('Table'[date])) return
MAXX(_table,[_pcs])

(Thanx a lot to @v-lili6-msft with this measure)

city	product #	date	pcs	sumup per day	max in month by product and city	exact date of max in month
Beijing	123544	January 01 2019	1	11	17	January 02 2019
Beijing	123544	January 01 2019	10	11	17	January 02 2019
Beijing	123544	January 02 2019	15	17	17	January 02 2019
Beijing	123544	January 02 2019	2	17	17	January 02 2019
Shanghai	123544	January 01 2019	3	15	15	January 01 2019
Shanghai	123544	January 01 2019	12	15	15	January 01 2019
Shanghai	123544	January 02 2019	12	12	15	January 01 2019

[v-yulgu-msft · Accepted solution]

Hi @Thoms ,

Please refer to below measures:

sumup per day =
CALCULATE (
    SUM ( Test_6[pcs] ),
    ALLEXCEPT ( Test_6, Test_6[city], Test_6[product #], Test_6[date] )
)

max in month by product and city =
MAXX (
    FILTER (
        ALLSELECTED ( Test_6 ),
        Test_6[city] = SELECTEDVALUE ( Test_6[city] )
            && Test_6[product #] = SELECTEDVALUE ( Test_6[product #] )
            && Test_6[date].[Year] = YEAR ( SELECTEDVALUE ( Test_6[date] ) )
            && Test_6[date].[MonthNo] = MONTH ( SELECTEDVALUE ( Test_6[date] ) )
    ),
    [sumup per day]
)

exact date of max in month =
CALCULATE (
    SELECTEDVALUE ( Test_6[date] ),
    FILTER (
        ALLSELECTED ( Test_6 ),
        Test_6[city] = SELECTEDVALUE ( Test_6[city] )
            && Test_6[product #] = SELECTEDVALUE ( Test_6[product #] )
            && Test_6[date].[Year] = YEAR ( SELECTEDVALUE ( Test_6[date] ) )
            && Test_6[date].[MonthNo] = MONTH ( SELECTEDVALUE ( Test_6[date] ) )
            && [sumup per day] = [max in month by product and city]
    )
)

Best regards,

Yuliana Gu

[Anonymous]

Hi Thoms

Perhaps something like;

Calculate(max('Table'[date]),filter('Table','Table'[date].[Month] = Table'[date].[Month] && 'Table'[date].[Year] = Table'[date].[Year]))

[Thoms]

Thanx dear for the feedback!

Sorry, but i guess was not precise.

I need exact date of max value in month. So need reference to column "max in month by product and city", how to add it to the dax?

[Anonymous]

OK.

So something like;

Calculate(max('Table'[date]),filter('Table','Table'[max in month by product and city] = Table'[max in month by product and city]))

However if you have 2 cities with the same [max in month by product and city] that might not work. You could add more filter criteria if you want this to be specific to a city and/or product