Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
denxx34
Frequent Visitor

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

1 ACCEPTED SOLUTION
amitchandak
Super User
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

View solution in original post

2 REPLIES 2
amitchandak
Super User
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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors