Count by End of Month--Reposting

[sunny27]

https://community.powerbi.com/t5/Desktop/Count-by-End-of-Month-Reposting/m-p/507546#M237114

Please go through the above link

For example for Student Number 30830 the Effective date for Status Change is on same day but there is other field i didn't mention in sample data is UpdatedDate.

StudentGUID	StudentNumber	StudentStatusID	Status	EffectiveDate	UpdatedDate
8136E11E-0A6A-4AB9-B7EE-315B72A27280	30830	2	Pending	8/2/2018	8/2/2018
F102367E-B10F-4D68-B1B9-AFA78550A8EF	30830	3	Approved	8/2/2018	8/30/2018

If the status is changed in same month it should show the updated Status in that month.

 For Pending below is the code, but we are getting above result in Pending and Approved.Please let me know if you have any questions and help me out on this.

 Pending2 = 
VAR __minDate =
 MIN ( 'Date'[Date] )
VAR __maxDate =
 MAX ( 'Date'[Date] )
VAR __tmpTable =
 SUMMARIZE (
 tblStudent,
 tblStudent[StudentGUID],
 tblStudent[StudentNumber],
 tblStudent[StudentStatusID],
 "__UpdatedDate", MAX ( [UpdatedDate] )
 )
VAR __tmpTable1 =
 ADDCOLUMNS (
 __tmpTable,
 "__Status", MAXX (
 FILTER (
 tblStudent,
 tblStudent[StudentGUID],
 = EARLIER ( [StudentGUID],)
 && [__UpdatedDate] = EARLIER ( [__UpdatedDate] )
 ),
 [StudentStatusID]
 )
 )
RETURN
 COUNTROWS (
 FILTER (
 __tmpTable,
 [__UpdatedDate] >= __minDate
 && [__UpdatedDate] <= __maxDate
 && tblStudent[StudentStatusID]=2 )
 )

Please help me out on this

[v-lili6-msft]

hi,sunny27

    I have tested your formula, there are a few extra commas in it. It works well, StudentNumber 30830 has one Pending

in Aug-2018.

Please share some data sample and the expected output.

Best Regards,

Lin