Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
  • danextian's avatar
    danextian
    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's avatar
Anonymous
Not applicable
1 year ago

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 

 

 

 

 

  • danextian's avatar
    danextian
    Super User
    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
    )