Forum Discussion
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...
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 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
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.
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,Thank you so much for this, it works now!
