cancel
Showing results for
Did you mean:
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
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]

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

Best Regards,

Amy

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

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