+ Reply to Thread
Results 1 to 6 of 6

DAX measure return top 3 dates for month Year / Month pivot

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    804

    DAX measure return top 3 dates for month Year / Month pivot

    Hi,
    i have a measure which almost works;

     Top3day:=VAR top1 = TOPN(3, VALUES( 'Calendar'[Date]), [TotalUnits], DESC) 
    VAR result =
    IF( [TotalUnits],
     CONCATENATEX( top1, 'Calendar'[Date], ",  ", [TotalUnits], DESC)) 
     RETURN  
     IF( ISFILTERED('Calendar'[Month]), result , BLANK() )
    Now this looks like it works but when it get's to the last Month in the table it goes mad and return all dates?

    I have included a power query version to check results, this returns all dates above or equal to 3rd highest "units" value for each Year/ Month group.
    When I change the measure to top 1 , then it works , not sure what's happening?

    Richard.
    Attached Files Attached Files

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,056

    Re: DAX measure return top 3 dates for month Year / Month pivot

    You're getting the blanks included because you only have 1 actual value. You could use something like this instead:

    =VAR top1 = FILTER(ADDCOLUMNS(SUMMARIZE( 'Calendar','Calendar'[Date]),"@TotalUnits", [TotalUnits]), NOT(ISBLANK([@TotalUnits])))
    VAR result =
    IF( [TotalUnits],
     CONCATENATEX( TOPN(3, top1, [@TotalUnits], DESC),'Calendar'[Date], ",  ", [TotalUnits], DESC)) 
     RETURN  
     IF( ISFILTERED('Calendar'[Month]), result , BLANK() )
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    804

    Re: DAX measure return top 3 dates for month Year / Month pivot

    Thanks, that does give a correct result, so lets see, if there a less than N values then all are returned ? is that it summed up.
    I have not gone through your measure but will do. thanks of the help. Can't give a rep but would if could.

    Richard.

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,056

    Re: DAX measure return top 3 dates for month Year / Month pivot

    Basically yes because all their values are equal and within the top N values.

  5. #5
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    804

    Re: DAX measure return top 3 dates for month Year / Month pivot

    so they are all tied for second and third place,

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,056

    Re: DAX measure return top 3 dates for month Year / Month pivot

    Correct. Honestly, it's a little odd to me, given how blank is usually ignored completely, but there we are.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Return a specific Year if the month is the last month of the current Year
    By MAJID1479 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-15-2022, 01:42 PM
  2. Replies: 3
    Last Post: 05-05-2022, 05:00 PM
  3. Replies: 4
    Last Post: 09-04-2020, 04:02 PM
  4. [SOLVED] How do return days,week no,month and year in between two dates
    By silambarasan.J in forum Excel General
    Replies: 5
    Last Post: 10-26-2016, 04:29 AM
  5. Return month and year from pivot table
    By potatoman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-08-2014, 07:28 AM
  6. Graphing dates by month/year with 2 bars per month/year
    By Ellpoyohlokoh in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-22-2014, 05:25 AM
  7. Help with Formulae to return a Month and Year only from a column of dates
    By rick.parry in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2012, 08:52 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1