cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## 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 ?

1 ACCEPTED SOLUTION
Super User

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))``````
Super User

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))``````

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors