Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
manubk
Helper I
Helper I

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. 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 

manubk_0-1737025010513.png

 

1 ACCEPTED 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
    )

danextian_0-1737074287540.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

7 REPLIES 7
manubk
Helper I
Helper I

 @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

 

 

 

manubk_0-1737040729861.png

 

manubk
Helper I
Helper I

@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 

 

manubk_0-1737039179763.png

 

 

 

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
    )

danextian_0-1737074287540.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
danextian
Super User
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

danextian_0-1737027013529.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@danextian  Thank you soo much for your help .. This solved my problem

@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.

danextian_1-1737036788335.png

 

Please see the attached pbix.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.