Calculated Column - Latest Entry

[Mat42]

I have a dataset that is a mix of quarterly data and annual data. It's similar to the simple example below:

The 'Current Quarter?' column is a calculated column that I've added that identifies whether the quarter listed in the 'Quarter' column is the period we're currently reporting on. It automatically updates when the next reporting period rolls around. You can see in the example that measures 1 and 3 both have a Y in the 'Current Quarter?' column because the period we're currently reporting on is quarter 3.

However, I can't work out how to add the 'Current Year?' column. It needs to identify the latest/most recent entry for a measure and add in a Y, as per the example above. In the example, measures 2 and 4 both have a Y in the most recent entry. An added wrinkle is that the annual data won't necessarily have 4 entries (in the example, measure 2 only has 3).

Any ideas?

[V-lianl-msft]

Hi @Mat42 ,

Does "measure" have corresponding date?

If so, try the DAX below:

Measure = IF(YEAR(TODAY())=MAX('Table'[Date].[Year]),"Y","N")

If the problem persists,could you please share more sample data or sample pbix?

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

[Mat42]

Thanks for your responses.

The problem is that what I've shared is basically all there is. The measures don't have an assigned date, just Q1 Q2 etc or the 2015/2016, 2018-2019 designations. The rest of the table is just service specific data (levels, amounts etc) that match the measure. The example table is the only data not input on a quarterly basis.

Is it possible, maybe, to use the Max function to create the column to add a Y to the last entry per annual measure? The quarter labels (Q1 etc) are easy to work with, but the year entries will vary. Sometimes the latest data will be 2019/2020, sometimes it might be 2016/2017. It really just needs to add a Y in for the latest year (for those measures that don't have Q1 etc.

[Mat42]

Thanks for everyone's help.

Turns out I was trying to do something that we don't actually need to do. Which would have been helpful to know a month ago.

Anyway, thanks for the help.

[amitchandak]

is this your raw data, on which you are working?

Try Like

if(period = (year(today()) &"-"& year(date(year(today()) ,year(today()) -12,day(today()) ))),"Y","N")