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
Anonymous
Not applicable

Measure to get value at start and end of date range defined by slicer

Hi All,

Wonder if you might be able to help me out with something please? I am trying to show the change in a period defined by a slicer and want to grab the value at the start and end of the date range defined by the slicer.

 

DateValue
01/01/202011
02/01/202054
03/01/20207
04/01/202023
05/01/202036

 

My data would look something like the above, my slicer slices on Date and if I selected 03/01/2020 to 05/01/2020 on my slicer I would want a measure to return 7 and one to return 36 so I can calculate the change between the start and end of the range.

I hope all of that makes sense, and thank you in advance for any help you can offer.

Mark

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous ,

Try like. Change position of _min and _max in formula as per need

Measure =
var _min = minx('Date','Date'[Date])
var _max = maxx('Date','Date'[Date])
return
calculate(Table[Value],filter('Date','Date'[Date]=_min))- calculate(Table[Value],filter('Date','Date'[Date]=_max))

Measure =
var _min = minx('Date','Date'[Date])
var _max = maxx('Date','Date'[Date])
return
calculate(Table[Value],filter(all('Date'),'Date'[Date]=_min))- calculate(Table[Value],filter(all('Date'),'Date'[Date]=_max))

Anonymous
Not applicable

Fantastic, I will give it a whirl and see what happens. 

 

In the meantime I think I have made it work with this:

 

Min Report Date = LOOKUPVALUE('Activity Tracking'[Units],'Activity Tracking'[Report Date],MIN('Count_Report'[Report Date]))

With same measure again for Max

and when I filter the dates with the slider the measures seem to show the correct values.

 

Does my soluution seem OK? (I have >500 rows of data so performance not too much of a concern) 

az38
Community Champion
Community Champion

Hi @Anonymous 

in your solution could be issues with filtering as it doesn't support row context.

If you sure your user scenarios doesn't affect to result, LOOKUPVALUE() is also ok

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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.