Forum Discussion

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

DAX Measure to return top n dates

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

darkniqht's avatar
darkniqht
Icon for Advocate I rankAdvocate I
1 year ago

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's avatar
    Dicken
    Icon for Post Prodigy rankPost Prodigy
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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.