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.
Hi All,
I am facing issue in getting one formula.. Request you to please help me .
Below is the condition.
Month Last ticket Next start time should be the Actual end last day of the month. For Example
In line 4 Next start time is showing 6/5/2024 8:39:35 AM .. Ideall it shoud be 5/31/2024 11:59:59 Pm or 6/1/2024 12:00:00
Same way for 7/6/2024 8:18:13Am should be 6/30/2024 11:59:59 PM or 7/1/2024 12:00:00 either any one date
Last ticket of the month next startime should be Actual end last day of the month
Solved! Go to Solution.
Try this:
New End Time =
// It determines whether the maximum start time within the same period matches the current row's start time.
// If they match, the end time is set to the end of the current month at 11:59:59 PM.
// Otherwise, the end time is set to the next earliest start time within the same period.
VAR MaxTicketStartTime =
// Find the maximum start time within the same period as the current row.
MAXX (
FILTER ( 'Table', 'Table'[Period] = EARLIER ( 'Table'[Period] ) ),
[Start DateTime]
)
VAR NextStart =
// Find the next start time that is greater than the current row's start time within the same period.
MINX (
FILTER (
'Table',
'Table'[Period] = EARLIER ( 'Table'[Period] )
&& 'Table'[Start DateTime] > EARLIER('Table'[Start DateTime])
),
[Start DateTime]
)
VAR LastTicket =
// Calculate the end of the month for the current row's start time, setting it to 11:59:59 PM.
EOMONTH ( 'Table'[Start DateTime], 0 ) + TIME ( 23, 59, 59 )
RETURN
// Compare the maximum start time to the current row's start time.
// If they are equal, return the calculated end-of-month time; otherwise, return the next start time.
IF (
MaxTicketStartTime = 'Table'[Start DateTime],
LastTicket,
NextStart
)
Proud to be a Super User!
@danextian I Have used the below formula to find out the next Start time . Hope this helps you to provide me the solution . I have sorted Ascending order for Actual Start and added Index to it.
Next Start Time =
VAR _nextindex =
CALCULATE (
MIN ( 'Incident'[Index] ),
FILTER ( 'Incident','Incident'[Index] > EARLIER ( 'Incident'[Index] ) )
)
RETURN
CALCULATE (
MAX ( 'Incident'[Actual Start] ),
FILTER ( 'Incident', 'Incident'[Index] = _nextindex )
) + 0
@danextian Thank you sooo much .. This the way i wanted for last ticket .. But only one issue is New End time should be the Second ticket Start time. AS you can see the below attachment Next start time date is the Actual start of the next ticket. FIrst Row Next start time is 5/21/2024 3:16:08 AM Which is Second ticket Start Time .
I tried to tweak the formula that you have proivded but i couldn't.
In your Formula New end time is same as End Date time , It should be 2nd row Start Date time
Try this:
New End Time =
// It determines whether the maximum start time within the same period matches the current row's start time.
// If they match, the end time is set to the end of the current month at 11:59:59 PM.
// Otherwise, the end time is set to the next earliest start time within the same period.
VAR MaxTicketStartTime =
// Find the maximum start time within the same period as the current row.
MAXX (
FILTER ( 'Table', 'Table'[Period] = EARLIER ( 'Table'[Period] ) ),
[Start DateTime]
)
VAR NextStart =
// Find the next start time that is greater than the current row's start time within the same period.
MINX (
FILTER (
'Table',
'Table'[Period] = EARLIER ( 'Table'[Period] )
&& 'Table'[Start DateTime] > EARLIER('Table'[Start DateTime])
),
[Start DateTime]
)
VAR LastTicket =
// Calculate the end of the month for the current row's start time, setting it to 11:59:59 PM.
EOMONTH ( 'Table'[Start DateTime], 0 ) + TIME ( 23, 59, 59 )
RETURN
// Compare the maximum start time to the current row's start time.
// If they are equal, return the calculated end-of-month time; otherwise, return the next start time.
IF (
MaxTicketStartTime = 'Table'[Start DateTime],
LastTicket,
NextStart
)
Proud to be a Super User!
Hi @manubk
Try this custom column
let
date_start = Date.StartOfMonth([Actual Start]),
date_end = Date.StartOfMonth([Actual End]),
result =
if date_end >date_start then DateTime.From( Date.EndOfMonth(Date.From([Actual Start]) ) ) + #duration(0,23,59,59) else [Actual End]
in result
Proud to be a Super User!
@danextian Thank you so much for your reply .. Hope i was wrong in explaining the question
Lets think for Jan month there are 5 tickets and for last ticket the Next start time should be 1/31/2025 11:59:59 PM Should be shown..
for Feb month there are 2 tickets and the last ticket Next start time should show 2/28/2025 11:59:59 PM
Same as for following months .. It would be great if you could provide me in DAX formula .. As Nextstart time is based on the dax formula and I cant enter this in Custom Column
You will need identify which period a start datetiem belongs to by creating a calculated column and then you need to create another calculated column to identify whether the end datetime is the max one for that period and if max return a different end datetime.
Please see the attached pbix.
Proud to be a Super User!
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
185 | |
92 | |
67 | |
62 | |
52 |