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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Matej
Helper I
Helper I

Value returned based on specific time requirement

Hello all, 

I'm trying to figure out an issues in my report. 

 

I have a list of items with cost that is being changed at random intervals (my current dataset includes data from 1/1/22 - 31/1/23). I have managed to write some measured to get first and last cost based on filter context: 

 

_lastOldIDC =
VAR tbl = VALUES('IDCs')
VAR max_date = MAXX('IDCs', [Maximum of Date])
VAR result =  MINX(FILTER(tbl, [Maximum of Date] = max_date), [Old Value])
RETURN
    result
 
_firstOldIDC =
VAR tbl = VALUES('IDCs')
VAR min_date = MINX('IDCs', [Maximum of Date])
VAR result =  MINX(FILTER(tbl, [Maximum of Date] = min_date), [Old Value])
RETURN
    result
 
snip.png
 
These two work great but now I need a similiar measure that will only return a value (cost change) that represents the last value in the column before 10/11/2022. In the example based on the screenshot the value returned would be £2.43 as a last value in the column before the treshold (10/11/22). My measures can filter out other values and dates if I use a slicer but that only captures defined space of time and not the last cost change done prior to 10/11/2022. 
Any help here would be massively appreaciated. 
1 ACCEPTED SOLUTION
sebastiandyrby
Resolver I
Resolver I

Hi

This could be done like this - is that what you're looking for?

sebastiandyrby_0-1675946385713.png

This returns the following with my dataset-copy:

sebastiandyrby_1-1675946424489.png

 

Best regards

View solution in original post

2 REPLIES 2
sebastiandyrby
Resolver I
Resolver I

Hi

This could be done like this - is that what you're looking for?

sebastiandyrby_0-1675946385713.png

This returns the following with my dataset-copy:

sebastiandyrby_1-1675946424489.png

 

Best regards

This is literally what I've been looking for. Many thanks for the help!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors