Forum Discussion
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...
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 )
Hi Anonymous
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
danextian Thank you soo much for your help .. This solved my problem