Sum(Values) based on the MIN of Date for each User_ID

[NilR]

In the below calculated table I have some highligthed IDs with more than one Engage_Dt and I only want to get their first/Min Engag_Date to sum([Value]) . how can I filter that?

Table = 
VAR Min_Date = "202207"
VAR Max_Date = "202306"
VAR _gpnb = "xxxx"
VAR _ACTIVE_CNSR_ID = GROUPBY(FILTER(‘Table’,([ENR_DT]>= Min_Date && [ENR_DT]<= Max_Date) && ‘Table’[GP_NB]=_gpnb && [_ID] <> "0" ),’Table’[_ID])

Return
 ADDCOLUMNS(SUMMARIZE( 
         FILTER(‘Table’, [_ID] IN _ACTIVE_CNSR_ID && ‘Table’[ENGAGED_DT] >= Min_Date && ‘Table’[ENGAGED_DT] <= MAX_Date 
        && (‘Table’[ENR_DT]>= Min_Date && ‘Table’[ENR_DT]<= Max_Date) 
        && ‘Table’[GP_NB]=_gpnb ),
         ‘Table’[_ID],  ‘Table’[ENGAGED_DT]),
        "__VALUE",  CALCULATE( IF(MIN(‘Table’[ENGAGED_DT]) , SUMX(‘Table’,[_VALUE]),0)))

_ID	ENGAGED_DT	_Value
11250087988	202302	5
16250030855	202302	5
21250195039	202302	5
2500180107	202302	5
41250066251	202211	8
51250171501	202211	8
51250171501	202212	7
51250171501	202304	3
56250063999	202302	5
57500135048	202304	3
60000341293	202302	4
60000341299	202302	5
60000341312	202302	4
6250314538	202302	5
6250406879	202302	5
65000313982	202212	7
7500252525	202306	1
82500034777	202302	5
82500359314	202305	2
87500356738	202306	1
92500092651	202302	5

Below Final Expected result :

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[NilR]

This is exactly correct but the total returns wrong!

[Ashish_Mathur · Accepted solution]

Corrected for now.

[Anonymous]

Hi @NilR ,

Please have a try.

Create a measure.

Measure = var _1=MINX(FILTER(ALL('Table'),'Table'[_ID]=SELECTEDVALUE('Table'[_ID])),'Table'[ENGAGED_DT])
return 
IF(MAX('Table'[ENGAGED_DT])=_1,1,BLANK())

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ToddChitt]

Create a new table with this statement:

My Table = SUMMARIZE('Table', 'Table'[_ID], "Min ENGAGE_DT", MIN('Table'[ENGAGE_DT] )

Now add a column that concatenates _ID and ENGAGE_DT from this table and the base table. Join on that column.

That should be a one-to-one relationship.

Now add a column = RELATED('Table'[Value])

[NilR]

Thank You! I am new to this and could not follow your direction and figure it out! 😔

[ToddChitt]

For the one row you have highlighted, what result are you expecting? Can you give us a few samples?

[NilR]

Thank you! below picture should be the finial: