Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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
))
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?
Proud to be a Super User!
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
))
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
User | Count |
---|---|
120 | |
72 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |