topic Re: Earliest values based on timeline in DAX Commands and Tips

Earliest values based on timeline

kalspiros — Fri, 15 Jan 2021 23:14:24 GMT

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!

Re: Earliest values based on timeline

AlB — Sat, 16 Jan 2021 16:17:14 GMT

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

Re: Earliest values based on timeline

kalspiros — Sat, 16 Jan 2021 19:59:13 GMT

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

Re: Earliest values based on timeline

AlB — Sat, 16 Jan 2021 22:42:52 GMT

kalspiros

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

Re: Earliest values based on timeline

kalspiros — Sun, 17 Jan 2021 11:17:58 GMT

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