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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

1 REPLY 1
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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.