topic Re: Find position with next smalles date compared to a user-entered date in DAX Commands and Tips

Find position with next smalles date compared to a user-entered date

sim_kay7 — Tue, 18 Mar 2025 09:45:51 GMT

Hello, I have a table with Orders and various Positions within each Order, which have a date allocated. I wanted to create a Measure that finds the maximum position within each order that is smaller than a user-entered date. So for a user-entered date of 05.02.2025 the measure shall only return the rows marked in grey in the sample table.

The following measure does exactly that:

Measure = VAR UserEnteredDate = [Selected_date] RETURN CALCULATE ( MAX ( 'Tabelle'[Position] ), FILTER ('Tabelle', 'Tabelle'[Date] = CALCULATE ( MAX ( 'Tabelle'[Date] ), ALLEXCEPT ( 'Tabelle', 'Tabelle'[Order] ), 'Tabelle'[Date] < UserEnteredDate ) ) )

As a next step I would like to count the number of orders within each Position in the context of the Measure above. So desired result is to get 2 orders in Position '2' and 1 order in Position '1'.

I tried many different options but can't seem to get the proper result, so I really hope you can help with this.

Re: Find position with next smalles date compared to a user-entered date

bhanu_gautam — Tue, 18 Mar 2025 09:51:21 GMT

sim_kay7 Measure to find the maximum position within each order that is smaller than the user-entered date:

DAX
MaxPositionBeforeDate =
VAR UserEnteredDate = [Selected_date]
RETURN
CALCULATE (
MAX ( 'Tabelle'[Position] ),
FILTER (
'Tabelle',
'Tabelle'[Date] = CALCULATE (
MAX ( 'Tabelle'[Date] ),
ALLEXCEPT ( 'Tabelle', 'Tabelle'[Order] ),
'Tabelle'[Date] < UserEnteredDate
)
)
)

Measure to count the number of orders for each position:

DAX
CountOrdersByPosition =
VAR UserEnteredDate = [Selected_date]
RETURN
SUMX (
VALUES ( 'Tabelle'[Order] ),
CALCULATE (
COUNTROWS ( 'Tabelle' ),
FILTER (
'Tabelle',
'Tabelle'[Position] = [MaxPositionBeforeDate] &&
'Tabelle'[Date] < UserEnteredDate
)
)
)

Re: Find position with next smalles date compared to a user-entered date

sim_kay7 — Tue, 18 Mar 2025 10:45:49 GMT

bhanu_gautam Thanks for your quick response! The first measure works fine, however the second measure "CountOrdersByPosition" doesn't deliver the expected result:

Any ideas how to modify the measure to make it work?

Re: Find position with next smalles date compared to a user-entered date

bhanu_gautam — Tue, 18 Mar 2025 10:49:05 GMT

sim_kay7 , Try using

dax
CountOrdersByPosition =
VAR UserEnteredDate = [Selected_date]
RETURN
SUMX (
VALUES ( 'Tabelle'[Order] ),
VAR MaxPos = CALCULATE (
MAX ( 'Tabelle'[Position] ),
FILTER (
'Tabelle',
'Tabelle'[Date] = CALCULATE (
MAX ( 'Tabelle'[Date] ),
ALLEXCEPT ( 'Tabelle', 'Tabelle'[Order] ),
'Tabelle'[Date] < UserEnteredDate
)
)
)
RETURN
IF (
MaxPos = [MaxPositionBeforeDate],
1,
0
)
)

Re: Find position with next smalles date compared to a user-entered date

sim_kay7 — Tue, 18 Mar 2025 12:52:19 GMT

bhanu_gautam I thought this would solve it. It does work properly for a user-entered date of 05.02.2025. Unfortunately for another date it returns a wrong result (e.g. 31.01.2025: expected result would be a count of 2 for Position '1' but it returns a count of 2 for each):

Re: Find position with next smalles date compared to a user-entered date

bhanu_gautam — Tue, 18 Mar 2025 13:07:55 GMT

sim_kay7 , Try this one

Re: Find position with next smalles date compared to a user-entered date

sim_kay7 — Tue, 18 Mar 2025 15:12:31 GMT

bhanu_gautam: The lastest measure seems to not respond to the user-entered date. Is it possible to attach the .pbix-file here, maybe that helps?

Re: Find position with next smalles date compared to a user-entered date

v-pnaroju-msft — Wed, 14 May 2025 10:59:45 GMT

Thankyou, bhanu_gautam, for your response.

Hi sim_kay7,

We appreciate your inquiry posted on the Microsoft Fabric Community Forum.

Please find attached the screenshot and the PBIX file that may assist in resolving the issue:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will aid other community members facing similar queries.

Should you have any further questions, please feel free to contact the Microsoft Fabric Community.

Thank you.

Re: Find position with next smalles date compared to a user-entered date

v-pnaroju-msft — Sat, 17 May 2025 18:12:34 GMT

Hi sim_kay7,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

Re: Find position with next smalles date compared to a user-entered date

v-pnaroju-msft — Tue, 20 May 2025 09:45:13 GMT

Hi sim_kay7,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

Re: Find position with next smalles date compared to a user-entered date

v-pnaroju-msft — Sat, 24 May 2025 02:03:38 GMT

Hi sim_kay7,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

Re: Find position with next smalles date compared to a user-entered date

v-pnaroju-msft — Sun, 06 Jul 2025 17:18:47 GMT

Hi sim_kay7,

We are following up to check if your query has been resolved. If you have found a solution, kindly share it with the community to help others facing similar issues.

If our response was helpful, please mark it as the accepted solution. This will benefit the wider community.

Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.