calculate sum by dates

[e_vera]

Hi,

I am new to DAX and have absolute minimal idea about formulas. Could someone please help me construct a measure that computes for the difference (amount) between the earliest of status 1 and the latest for status 2 filtered by ticket? In the below screenshot, for example, it should be:

Ticket 0001 > -120 (earliest of status FINE_OFFSET) + 50 (latest of status FINE) = 70 (correct answer for Ticket 0001)

Ticket 0002 > 0 + 150 = 150

Thank you in advance!

[parry2k]

@e_vera try this measure

Measure 1 = 
VAR __fineOffset = 
    CALCULATE ( 
        MAX ( Fine[Amount] ), 
        TOPN( 1, FILTER( ALL ( Fine ), Fine[Ticket] = MAX ( Fine[Ticket] ) &&    Fine[Flag] = "Fine_Offset" ), Fine[Date/Time], ASC ) 
    )
VAR __fine = 
    CALCULATE ( 
        MAX ( Fine[Amount] ), 
        TOPN( 1, FILTER(   ALL ( Fine ), Fine[Ticket] = MAX ( Fine[Ticket] ) &&    Fine[Flag] = "Fine" ), Fine[Date/Time], DESC ) 
   )

RETURN 
__fineOffset + __fine

[e_vera]

hi parry2k,

Thank you so much for your suggestion. Unfortunately, the formula is not returning any value. I've tried removing the second var as well (keeping only fineOffset) but it's still blank.

[v-lid-msft · Accepted solution]

Hi @e_vera ,

We can create a measure to meet your requirement:

Measure =
VAR FirstTimeForStatus1 =
    CALCULATE ( MIN ( 'Table'[Date/Time] ), 'Table'[Status] = "FINE_OFFSET" )
VAR LastTimeForStatus2 =
    CALCULATE ( MAX ( 'Table'[Date/Time] ), 'Table'[Status] = "FINE" )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        'Table'[Status] = "FINE_OFFSET",
        'Table'[Date/Time] = FirstTimeForStatus1
    )
        + CALCULATE (
            SUM ( 'Table'[Amount] ),
            'Table'[Status] = "FINE",
            'Table'[Date/Time] = LastTimeForStatus2
        )

Best regards,

[e_vera]

Thank you so much for this, it works now!

[parry2k]

@e_vera how you are using it? Did you put Ticket column in the table visual and this measure? Share the screen shot. Do you have any filter or relationship that has impact. Provide more details.