Forum Discussion

e_vera's avatar
e_vera
Icon for Helper I rankHelper I
6 years ago
Solved

calculate sum by dates

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!

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      e_vera
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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,