Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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.
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.
@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]))
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |