Min Date by Category and Next Available Date

[villa1980]

2 in 1 here for you.

 I have a Table that has Group and a StartDate and free minutes. However, the group can have a number of StartDates. As Below
Group 1    05/11/2024   20

Group 1    06/11/2024   0

Group 1    07/11/2024   240

What I would like to do is find the min and max start date for the group, so is used in another calc.

This calc  is Next Available Day, I may be making the issue more complicated then it needs to be.
My thought is if the free minutes is 0 on the Start Date and is greater than Min Start Date and Min Start Date and Free minutes between 1 and 540 then Min Start Date, if both Min Start Date and Start Date have free minutes of 0, then Start Date +1, otherwise Min Start Date.

If you have a better way of calculating this then please let me know, am open to any ideas.

Thanks

Alex

[ryan_mayu]

not clear about the logic below

if the free minutes is 0 on the Start Date and is greater than Min Start Date and Min Start Date and Free minutes between 1 and 540 then Min Start Date,

could you pls elaborate more on this?

and what's the expected output based on the sample data you provided?

[Irwan · Accepted solution]

hello @villa1980 

is this match to your expected result from your sample data above?

Min Date =
var _Group = SELECTEDVALUE('Table'[Group])
var _Date = SELECTEDVALUE('Table'[Start Date])
var _Minute = SELECTEDVALUE('Table'[Free Minute])
var _MinimumDate = MINX(FILTER(ALLSELECTED('Table'),'Table'[Group]=_Group),'Table'[Start Date])
Return
IF(
    (
        _Minute=0&&_Date>_MinimumDate
    )&&
    (
        _MinimumDate&&
        (
            _Minute>1||
            _Minute<540
        )
    ),
    _MinimumDate,
IF(
    CALCULATE(MAX('Table'[Free Minute]),FILTER('Table','Table'[Group]=_Group&&'Table'[Start Date]=_MinimumDate))=0&&
    CALCULATE(MAX('Table'[Free Minute]),FILTER('Table','Table'[Group]=_Group&&'Table'[Start Date]=_Date))=0,
    _Date+1,
    _MinimumDate
))

if this is not your desired outcome, please provide your sample data along with the desired result from that sample data.

Thank you.

[villa1980]

Hello, apologies for the delay in replay, it did work however, I would now like the minum start date by town.So the Group associated to a particular town.

I have also noticed I have the minimum start date but I have a random date of 31/12/1899 appear in some, any idea why this would be happening?

Thanks