TopN using offsets error

[MarkEden]

Hi All,

I'm  fairly new to DAX and struggling with something that seems like it should be fairly simple.

I'm trying to create a TopN measure to use as a filter with the month offset column in my date table and visualise the most recent N months from the selected value. This is the fomula I've written but it only returns the value for the selected month (ie Top 1) instead of the top 12. 

I'd appreciate any advice on where I've gone wrong here. I'm trying to return top 12 values not rows.

TopN = 
var maxdate = MAX('Date Table'[MonthOffset])
RETURN
TOPN( 12,
CALCULATETABLE( 
    VALUES('Date Table'[MonthOffset]),
    'Date Table'[MonthOffset] <= maxdate),
     'Date Table'[MonthOffset],
     DESC
)

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but please try below whether it suits your requirement.

TopN =
VAR maxdate =
    MAX ( 'Date Table'[MonthOffset] )
RETURN
    TOPN (
        12,
        FILTER (
            ALL ( 'Date Table'[MonthOffset] ),
            'Date Table'[MonthOffset] <= maxdate
        ),
        'Date Table'[MonthOffset], DESC
    )

[MarkEden]

@Jihwan_Kim thank you. I think I'm close but am getting the error "a table of multiple values was supplied where a single value was expected".

I thought this might be because multiple dates in a month will have the same month offset, so created and tried a day offset but this gave the same result.

So you know how this might be modified to work correctly?

@tamerj1 - As requested the below screen shot is the visual I am trying to filter. Currently this shows a period to the beginning of the data, but I want to filter it to the last 12 months only based on the selected month & year. I think I need to use offset columns to do this as filtering the visual based on dates doesn't seem to work. So I think I need to create a measure that returns true if the date is within 12 months of the selected and then use that as a filter on the visual, but open to other suggestions from more knowledgable users.

My TopN measure as it is returns true only the selected month and year. Here June 2022 is selected:

I have a data model with a one to may relationship from my date table to my fact table, with year, month and day offset columns in the date table.

Hope this clarifies what I'm trying to do, but please let me know if any other queries.

Thanks in advance

[tamerj1]

Hi @MarkEden 

would you please explain in more details what are you trying to achieve perhaps with some screenshots. Your Topn measure returns a table! Are trying to create a calculated table?