Dax Prior Year of Event Day ( text field) in date dimension. Different day/month each yer

[Jachat]

We have events that are run every year. These events are in our Date Dimension as a text field, eg Mothers Day Lunch. The day/month is not the same every year. There are approx 50 events that are run annually.

In a power BI, if a user selects "Mothers Day Lunch" and Season 20/21, we would like to have a measure that shows comparative to prior 2 years.

How do we do this, as the field is not date format?

[amitchandak]

@Jachat , You need to have seprate date table, With Rank column suggested below on season/year.  on on season/event ( "Mothers Day Lunch").

Event should part of date table to should be in a seperate table

New column

Rank on Season = rankx(all(Date), [Season], ,asc,dense)

measure

This Year = CALCULATE(sum('Table'[Value]), FILTER(ALL('Date'),'Date'[Rank on Season]=max('Date'[Rank on Season])))
Last Year = CALCULATE(sum('Table'[Value]), FILTER(ALL('Date'),'Date'[Rank on Season]=max('Date'[Rank on Season])-1))

[Jachat]

I am trying to do this in SSAS tabular model

Does this look correct? As its giving me an error

=RANKX(all('Event Date','Event Date'[Racing Season],,asc,dense))

[amitchandak · Accepted solution]

@Jachat , Try

=RANKX(all('Event Date'),'Event Date'[Racing Season],,asc,dense)