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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
villa1980
Resolver I
Resolver I

Min Date by Category and Next Available Date

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

 

 

1 ACCEPTED SOLUTION
Irwan
Super User
Super User

hello @villa1980 

 

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

Irwan_0-1730335052834.png

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.

View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Irwan
Super User
Super User

hello @villa1980 

 

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

Irwan_0-1730335052834.png

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.

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.