Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Kingnico63000
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 ?

 

Thank you in advance 

 

 

1 ACCEPTED SOLUTION
sevenhills
Super User
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))

View solution in original post

1 REPLY 1
sevenhills
Super User
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))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.