Show top 10 of a table in the last calendar month

[rverstegen1]

Hello all,

I hope you can give me a little help here cause im pretty stuck at the moment.

I have a table that contains the following columns:

Name

Estimated

Realised

Difference (DAX code: Difference = [Realised]-[Estimated] )

Date 

Now what i want to show in the dashboard is the complete rows from the top 10 difference.

I can do that with using TopN like: TopN =TOPN(10,DataFile, [Difference])
But i need the top 10 rows where Name is not empty and where the top 10 is in the last calendar month.

If i try that by filtering the visual i will only get the rows that meet the criteria and are in the top 10 of the total file.

The purpose is that when you refresh the dashboard the top 10 changes to a new top 10 when there is a new calendar month (or the source data is changed)

Thanks in advance

[v-rzhou-msft]

Hi @rverstegen1 

You can try to build a filter measure and add this measure into visual filter.

Sample:

Filter Measure:

Filter Measure =
VAR _MonthLast =
    MONTH ( TODAY () )
VAR _MonthDateFile =
    MONTH ( MAX ( DateFile[Date] ) )
VAR _TOPN =
    CALCULATETABLE (
        VALUES ( DateFile[Difference] ),
        TOPN (
            10,
            FILTER (
                ALL ( DateFile ),
                AND ( _MonthDateFile = _MonthLast, NOT ( DateFile[Name] = BLANK () ) )
            ),
            DateFile[Difference]
        )
    )
RETURN
    IF ( MAX ( DateFile[Difference] ) IN _TOPN, 1, 0 )

Add this measure into visual filter and set it to show items when the value equal to 1.

Best Regards,

Rico Zhou

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

[rverstegen1]

Hi @v-rzhou-msft 

Thanks a lot for your help so far!

Almost the solution i think, there are 2 issues with this for me:

1. it still gives me the results of the current month (june) instead of the last month (may)

2. i get more then 10 results, could that be because i have more then 10 values equal in difference as highest value? (and if so is there a way to random limit it to 10?)

[v-rzhou-msft · Accepted solution]

Hi @rverstegen1 

Try this measure, add this measure into visual filter and set it to show items when value =1.

Filter Measure =
VAR _LastMonthStart =
    EOMONTH ( TODAY (), -2 ) + 1
VAR _LastMonthEnd =
    EOMONTH ( TODAY (), -1 )
VAR _CurrentDate =
    MAX ( DateFile[Date] )
VAR _CurrentName =
    MAX ( DateFile[Name] )
VAR _CurrentDifference =
    MAX ( DateFile[Difference] )
VAR _TOPN =
    TOPN (
        10,
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( DateFile ),
                DateFile[Date] >= _LastMonthStart
                    && DateFile[Date] <= _LastMonthEnd
                    && DateFile[Name] <> BLANK ()
            ),
            DateFile[Difference]
        ),
        [Difference]
    )
VAR _Result =
    IF (
        _CurrentDate >= _LastMonthStart
            && _CurrentDate <= _LastMonthEnd
            && _CurrentName <> BLANK ()
            && _CurrentDifference IN _TOPN,
        1,
        0
    )
RETURN
    _Result

Result:

TopN will show Top N values instead of only 10 values. I think there are some same values in your table so you will get result more than 10. You see here I have two values equal to 72. So Top10 will get 11 results.

Best Regards,

Rico Zhou

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

[amitchandak]

@rverstegen1 , Create a measure like this and take top 10 of that

measure = calculate([Difference], filter(Table, not(isblank(Table[Name]))),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

TOP N Measure =
CALCULATE([measure],TOPN(10,allselected('Table'[Name]),[Measure],DESC),VALUES('Table'[Name]))

[rverstegen1]

Thanks for the help @amitchandak 

I get an error for the measure saying it cant determine the value for difference, the column does not exist or there is no current row for this column.

Is that because the column is made with a DAX syntax?