Forum Discussion
Earliest values based on timeline
Hi All,
i think that can be solved with a CALCULATETABLE that will filter the sliced data and then return the EARLIEST but not too savvy with such sets, plus, from my research on this forum, i didn't manage to make EARLIEST work.
I would be looking for a measure that will be always returning the earliest [fx Average Value] of (Data[Point]) based on the (Data[Date]) that will be dynamically slicing the data.
Something like the images below (red boxes indicate the needed values)
Many thanks in advance!
I'd misread. Then use that earliers date to apply it to your measure:
New measure = VAR date_ = CALCULATE ( MIN ( DateTable[Date] ), ALLSELECTED ( DateTable[Date] ) ) RETURN CALCULATE ( [fx Average Value], DateTable[Date] = date_ )If this doesn't work share the pbix
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
4 Replies
I'd misread. Then use that earliers date to apply it to your measure:
New measure = VAR date_ = CALCULATE ( MIN ( DateTable[Date] ), ALLSELECTED ( DateTable[Date] ) ) RETURN CALCULATE ( [fx Average Value], DateTable[Date] = date_ )If this doesn't work share the pbix
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hey AIB,
After several issues and tiny amendments on other slicers and filters, that now does the trick!
Glad that VAR is working in Excel as well (wasn't on BI environment) as i did try it in the past to no avail.
All in all, many thanks for your help on this and for opening the VAR unlimeted possibilites for me!
Kind Regards
Hi kalspiros
EARLIEST does not have anything to do with this but rather with nested row contexts. Check it out: https://dax.guide/earliest/
Try
CALCULATE ( MIN ( DateTable[Date] ), ALLSELECTED ( DateTable[Date] ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Many thanks AIB for your reply,
Even though the measure above does succesfully provide the first date of the slicer, i would be actually looking for it to return the [fx Average Value] on the first date of the slicer.
Let me provide some more context: i want to collapse all lines to zero on the first date of the slicer. i don't want these lines to eternally increase in the years to come. in a way, i want to benchmark these lines based on the desired start date. that is why the value on the earliest date is important so that i'll subtract it from my [fn Average Value]
Hope that helps!
Kind Regards
