Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mat42
Helper III
Helper III

Calculated Column - Latest Entry

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

Annotation 2020-02-25 090007.png

 

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?

4 REPLIES 4
V-lianl-msft
Community Support
Community Support

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.

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.

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
Super User
Super User

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")

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.