DAX Measure to return top n dates

[Dicken]

Hi I set up a simple tabel of date , item and units and a pivot of year month and wanted to 
returnn the top 3 dates for each month but I keep getting all of hte dates in each month; 
as a test is tried; this ;

TopItem:=VAR top3 = TOPN( 3, VALUES( Table1[Item]), [TotlalUnits], DESC )

VAR result = CONCATENATEX( top3, Table1[Item], ", ", [TotlalUnits] ,DESC )

RETURN IF( ISFILTERED('Calendar'[Month]),result,BLANK())

This does work but if I replace the item with the Calandar table date 

TopDay:=VAR top3 = TOPN( 3, VALUES( 'Calendar'[Date]), [TotlalUnits], DESC )

VAR result = CONCATENATEX( top3, 'Calendar'[Date], ", ", [TotlalUnits] ,DESC )

RETURN IF( ISFILTERED('Calendar'[Month]),result,BLANK())
Then for each monht I am just getting all the dates returned ? 
Richard. 

[darkniqht]

Hi Richard! It looks like the issue is that the TOPN function is applied to the Calendar[Date] column, but it doesn’t consider the context of your month filter correctly.

Instead of using VALUES('Calendar'[Date]), try creating a measure that filters the dates based on the current month context. Here’s a modified version of your DAX:

TopDay =
VAR CurrentMonth = MAX('Calendar'[Month])
VAR top3 = 
    TOPN(
        3,
        FILTER(Table1, MONTH(Table1[Date]) = CurrentMonth),
        [TotalUnits], 
        DESC
    )
VAR result = CONCATENATEX(top3, Table1[Date], ", ", [TotalUnits], DESC)
RETURN IF(ISFILTERED('Calendar'[Month]), result, BLANK())

This way, it filters the table to only include the relevant dates for the current month before selecting the top 3. Give it a try and see if that works!

[Dicken]

Thanks I also tried this for 'day'  distinct day by adding an index ; 

Distinct top day:=

VAR tops = TOPN( 3, SUMMARIZE(Table1,Table1[Index],'Calendar'[Day Of Week]) ,[TotalUnits],DESC)

VAR sumt = SUMMARIZE( tops, 'Calendar'[Day Of Week])

RETURN CONCATENATEX(sumt, 'Calendar'[Day Of Week],", ")
Richard 

[Anonymous · Accepted solution]

Thanks for the reply from darkniqht , please allow me to provide another insight:

Hi, @Dicken 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

TopDay = 
VAR CurrentMonth =
    MAX ( 'Calendar'[Month] )
VAR top3 =
    TOPN (
        3,
        FILTER ( ALLSELECTED ( 'Table' ), MONTH ( 'Table'[Date] ) = CurrentMonth ),
        'Table'[Units], DESC
    )
VAR result1 =
    CONCATENATEX ( top3, 'Table'[Date], ", ", 'Table'[Units], DESC )
RETURN
    IF (
        ISFILTERED ( 'Calendar'[Month] ),
        IF (
            MAX ( 'Table'[Date] ) IN SELECTCOLUMNS ( top3, "2", 'Table'[Date] ),
            result1,
            BLANK ()
        ),
        MAX ( 'Table'[Date] )
    )

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.