cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jrhessey Frequent Visitor
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

Accepted Solutions
v-xicai Super Contributor
Super Contributor

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

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.

 

2 REPLIES 2
v-xicai Super Contributor
Super Contributor

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

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.

 

jrhessey Frequent Visitor
Frequent Visitor

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

@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]))