Forum Discussion
DAX: Dynamic Filter on Min and Max selected Date
I greet you,
just stuck on the following problem:
3 tables: Item, Stock, Calendar
The stock of the article is saved per day.
What I need...
1. I choose a period (From- To in a slicer from Calendar)
2. I need the value of the from-date for each article. (Min of selection)
3. And the value of the to-date. (Max of selection)
After that I need to calculate a difference to see the change per item in the selected period.
If an item starts later than the selected from date, then 0 should be returned as the result.
Example with two items:
Stock:
| ItemNo | Date | Quantity | ||
| 4711 | 01.01.2023 | 10 | ||
| 4711 | 02.01.2023 | 12 | ||
| 4711 | 03.01.2023 | 8 | ||
| 4711 | 04.01.2023 | 11 | ||
| 4711 | 05.01.2023 | 9 | ||
| 4711 | 06.01.2023 | 8 | ||
| 4711 | 07.01.2023 | 11 | ||
| 4712 | 03.01.2023 | 2 | ||
| 4712 | 04.01.2023 | 4 | ||
| 4712 | 05.01.2023 | 5 | ||
| 4712 | 06.01.2023 | 7 | ||
| 4712 | 07.01.2023 | 4 |
Item:
| ItemNo | ItemName | |
| 4711 | A | |
| 4712 | B |
Result for the selection 01/Jan/2023 - 06/Jan/2023 should be:
| ItemNo | MinValue | MaxValue | Delta | |||
| 4711 | 10 | 8 | -2 | |||
| 4712 | 0 | 7 | 7 |
Thank you very much in advance!!
denxx34 , Try a measure like
//Date table and Date is Joined
Delta =
var _max = maxx(allselected(Date),Date1[Date])
var _min = minx(allselected(Date),Date1[Date])
return
calculate( Max(Table[Quantity]), filter('Date', 'Date'[Date] =_max )) - calculate( Max(Table[Quantity]), filter('Date', 'Date'[Date] =_min))You can sperate min and max two have two more measures
2 Replies
- amitchandak
Super User
denxx34 , Try a measure like
//Date table and Date is Joined
Delta =
var _max = maxx(allselected(Date),Date1[Date])
var _min = minx(allselected(Date),Date1[Date])
return
calculate( Max(Table[Quantity]), filter('Date', 'Date'[Date] =_max )) - calculate( Max(Table[Quantity]), filter('Date', 'Date'[Date] =_min))You can sperate min and max two have two more measures
This post was very helpful to me in resolving a similar issue. Thanks!
