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
jrhessey
Frequent Visitor

Trying to get values from columns based on beginning and ending date slicer values

I'm trying to show the difference between 2 dollar amounts based on the beginning date and end date values set by a date slicer.  I've got data like such 

 

partnumber     inventorydate     inventorydollars

12345              5/1/2019             190

12345              5/2/2019             180

12345              5/3/2019             200

12345              5/4/2019             140

12345              5/5/2019             160

 

I have a date slicer set for a range between 5/2/2019 and 5/4/2019.

 

I have created a minumumDate and maximumDate which calcs to the values selectedcorrectly when I change the slicer.

 

minimumDate = CALCULATE(MIN(HistoricalQtyOnHand[InventoryDate]),ALLSELECTED(HistoricalQtyOnHand[InventoryDate]))

and 

MaximumDate = CALCULATE(MAX(HistoricalQtyOnHand[InventoryDate]),ALLSELECTED(HistoricalQtyOnHand[InventoryDate]))

So I set up another column in my table and used an if formula thinking it would only show the data equal to the min and max dates, but it showed everything.

MinDateInventoryDollars = if(HistoricalQtyOnHand[InventoryDate] = [Minimum date], HistoricalQtyOnHand[dollarsOnHand], 0)

returns below

 

partnumber     inventorydate     inventorydollars    MinDateInventoryDollars 

12345              5/1/2019             190                        190

12345              5/2/2019             180                        180

12345              5/3/2019             200                        200

12345              5/4/2019             140                        140

12345              5/5/2019             160                        160

 

What I would like to see is

 

partnumber     inventorydate     inventorydollars    MinDateInventoryDollars      MaxDateInventoryDollars 

12345              5/1/2019             190                        0                                            0

12345              5/2/2019             180                        180                                        0

12345              5/3/2019             200                        0                                            0

12345              5/4/2019             140                        0                                            140

12345              5/5/2019             160                        0                                            0

 

eventually pared down to

 

partnumber     MinDateInventoryDollars      MaxDateInventoryDollars     difference

12345              180                                         140                                        -40

 

Not sure why that formula doesn't work.  I found some other posts on here, but that just led me further down the rabbit hole confusing me more...  ha!

 

I've tried the date table filtering a visual table, but I can only choose top/bottom 1 and that doesn't get me what I need.  

 

Any help is greatly appreciated!!

 

Thanks!

 

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @jrhessey ,

 

You can create measures like DAX below.

 

MinDateInventoryDollars = CALCULATE(MAX(Table1[inventorydollars]),FIRSTDATE(Table1[inventorydate]))

 

MaxDateInventoryDollars = CALCULATE(MAX(Table1[inventorydollars]),LASTDATE(Table1[inventorydate]))

 

difference = Table1[MaxDateInventoryDollars]-Table1[MinDateInventoryDollars]

 

2.png

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
v-xicai
Community Support
Community Support

Hi @jrhessey ,

 

You can create measures like DAX below.

 

MinDateInventoryDollars = CALCULATE(MAX(Table1[inventorydollars]),FIRSTDATE(Table1[inventorydate]))

 

MaxDateInventoryDollars = CALCULATE(MAX(Table1[inventorydollars]),LASTDATE(Table1[inventorydate]))

 

difference = Table1[MaxDateInventoryDollars]-Table1[MinDateInventoryDollars]

 

2.png

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@v-xicai , first off thanks for your reply!  I didn't think it should be this difficult, but I beat my head against the wall for a couple of days and you post a simple solution like this...  ha!

 

I didn't include multiple part numbers in my example, however, changing max to sum fixed the numbers and matches exactly.  Thanks for taking the time to post a solution!  I really appreciate it!!

 

Just to be clear here is what I changed the formulas to

 

MinDateInventoryDollars = CALCULATE(SUM(Table1[inventorydollars]),FIRSTDATE(Table1[inventorydate]))
MaxDateInventoryDollars = CALCULATE(SUM(Table1[inventorydollars]),LASTDATE(Table1[inventorydate]))

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.