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

[sim_kay7]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[bhanu_gautam]

@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
)
)
)

[sim_kay7]

@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?

[bhanu_gautam]

@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
)
)

[sim_kay7]

@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): 

[bhanu_gautam]

@sim_kay7 , Try this one

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 = CALCULATE (
MAX ( 'Tabelle'[Position] ),
FILTER (
'Tabelle',
'Tabelle'[Date] < UserEnteredDate
)
),
1,
0
)
)

[sim_kay7]

@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?