Forum Discussion
Solved
Sum elements based on first date
Hello,
I have value like this
ID Date Statut Value
1 1/01 Submitted 1
1 1/01 Submitted -1
2 2/01 Accepted 1
2 2/01 Accepted -1
1 3/01 Submitted 1
3 4/01 Verified 1
3 6/01 Verified 1
7 7/01 Submitted 1
I want to count the numbers of value that = 1, based on most ancient date, by ID , only filter on Submitted
Exemple :
Numbers of Submitted = 2 (1 with 1/01 and 7 with 7/01)
Do you know how it's possible ?
Thank you in advance
See if this DAX can be helpful:
Latest Date Total per Status = var _LatestDate =LASTDATE('Table'[Date]) var _LatestDateStatus = LOOKUPVALUE('Table'[Status], 'Table'[Date], _LatestDate) -- return _LatestDate -- if you want to quick check -- return _LatestDateStatus -- if you want to quick check RETURN CALCULATE( -- sum('Table'[Value]) DISTINCTCOUNT('Table'[ID]) , FILTER( ALLSELECTED('Table'), 'Table'[Status] = _LatestDateStatus))
1 Reply
See if this DAX can be helpful:
Latest Date Total per Status = var _LatestDate =LASTDATE('Table'[Date]) var _LatestDateStatus = LOOKUPVALUE('Table'[Status], 'Table'[Date], _LatestDate) -- return _LatestDate -- if you want to quick check -- return _LatestDateStatus -- if you want to quick check RETURN CALCULATE( -- sum('Table'[Value]) DISTINCTCOUNT('Table'[ID]) , FILTER( ALLSELECTED('Table'), 'Table'[Status] = _LatestDateStatus))