Forum Discussion
Anonymous
1 year agoNot applicable
Eomonth last date
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...
- 1 year ago
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 )
Anonymous
1 year agoNot applicable
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