Dynamic set analysis for year and quarter

[michelle22]

Hi,

I currently have a table that looks something like this:

Year & Quarter	Data
2020 Q1	11
2020 Q2	5
2020 Q3	2

In my chart, I want that each time I update the table with a new row, the data for the latest Year & Quarter is displayed, as opposed to this, which I'd have to update every time: Sum((<[Year & Quarter]={'2020 Q3'}>) [Data])

I have tried using MaxString without any luck. While this does return the current Year & Quarter, I can't use it within the set analysis to grab the data. Max doesn't work I assume because it's not a number. I've also tried grabbing the last row of data, but can't figure that out either.

Thanks! I would be grateful for any advice. I should also mention that this table is associated with other tables using [Year & Quarter] as the association. 

[Anil_Babu_Samineni · Accepted solution]

Since some string is involved it always recommend to interpret your values to dual like

I have converted your field to

LOAD Data, Dual([Year & Quarter], QuarterStart(Makedate(Left([Year & Quarter],4)),Right([Year & Quarter],1)-1)) as [Year & Quarter]
INLINE [
Year & Quarter, Data
2020 Q1, 11
2020 Q2, 5
2020 Q3, 2
];

and expression is this

Sum({<[Year & Quarter]={"$(=MaxString([Year & Quarter]))"}>} Data)