Forum Discussion
TopN using offsets error
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
)
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