Forum Discussion
Eomonth last date
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 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.
danextian Thank you soo much for your help .. This solved my problem