Count by End of Month--Reposting

[sunny27]

Hi, I am wondering if anyone here can help!

We have an Student table that has StudentGUID,StudentNumber,EffectiveDate and StudentStatus

I need to somehow work out how many Students were Pending at the end of each month and if the Student is Pending and Approved in the same month then it should be considered as Approved at the end month. A Student Status is changed when he joins the school something like Approved,Closed,Pending,Revoked,Regular.There is EffectiveDate which is latest date for all the status.

So for an example, if a Student status is pending on May 2nd, 2017 and Approved on May20th, 2017 - this would not be included. If a Student status is pending on April 2nd 2017, and Approved on May 5th 2017, this would be included for April as it went over the end of the month mark.

If a Student status is pending on April 2nd 2017, and Approved on 8th Jul 2017 it would be included for April, May and June.

Thanks for any help.Below is the sample data.

StudentGUID	StudentNumber	StudentStatusID	Status	EffectiveDate
C0912B29-47FE-4D76-9E7B-2524C2E3F62D	22727	10	Declined	4/18/2013
E5D680EE-FD21-4130-9558-252F707D3125	30542	4	Pending	1/21/2015
65E959E9-9BBF-45A4-9878-25300937F568	29301	4	Approved	12/1/2016
3D9923CD-9A08-44FA-BB6E-2531B9EACCB5	28505	3	Revoked	11/8/2012
D7294F81-14AB-418F-B8DD-25359F4808A7	28251	4	Approved	6/14/2013
5EF2B303-2909-4A10-8A78-253698631037	26918	3	Revoked	2/9/2010
ABA2E14D-D0C9-47CB-B588-2536D75C6945	30736	1	NPR	4/26/2018
1071A47A-1B74-4251-9F3D-2538DEB69C7B	28251	2	Pending	5/22/2012
5F4E599E-AD15-404F-A718-254A21972938	29301	2	Pending	6/24/2014
D510C3B3-50B0-4E1D-B3DD-2558D6E9C3E4	27740	3	Revoked	5/30/2011
4FFA9BBA-7305-4DAF-BC57-25599A3DE0A4	27336	3	Revoked	9/14/2010
B4287B50-758A-49C5-949E-255B50B15067	30542	2	Approved	8/1/2017
138697DC-C3AF-41C0-9A14-255C115787C7	28426	10	Declined	1/15/2013
DE09F26A-936F-44B4-A574-256547858D6E	28505	1	NPR	4/22/2013
C75BEDF8-9E17-4D73-9F1E-256F3EEAE89E	23333	4	Approved	5/22/2006

[Greg_Deckler]

For that set of data, I do not see that it includes the use cases that you identify, can you include more data? Or am I not understanding you need?

Also, it would help if you included the expected results from your sample data.

[sunny27]

Hello Greg,

Thanks for responding and below is the sample data.

StudentGUID	StudentNumber	StudentStatusID	Status	EffectiveDate
7DA00144-01E2-4B04-9D8B-C8631C96AAF9	30868	2	Pending	8/30/2018
7526CA10-1497-4AE5-868F-5D45756074E0	30867	2	Pending	8/31/2018
7A19F945-00EC-4035-ABF7-656B57FBC26F	30866	2	Pending	8/31/2018
0B4064C9-0A51-4AAE-8B80-FE3CFF023749	30865	2	Pending	8/31/2018
C3076344-55A4-47B7-BD89-9618FC6281FC	30864	1	NPR	8/31/2018
55B7B2A7-8410-4F27-9C08-B82023273361	30863	1	NPR	8/30/2018
F5BFEBB6-C742-4A39-A327-44DEEACB2449	30862	2	Pending	8/30/2018
5E0BE9EB-5DA3-4B3F-A204-504F8A3DF1ED	30861	1	NPR	8/30/2018
A6E0FC55-CA99-480D-B112-5EC69E795296	30860	1	NPR	8/29/2018
B193F172-897F-4483-9346-0C7B58DB5117	30859	1	NPR	8/28/2018
D4DA7712-DEA9-49DF-B4B8-679A6CEB6E25	30858	1	NPR	8/27/2018
92D3A9BB-7CA8-44FE-91FB-AE96B31B4871	30857	2	Pending	8/31/2018
E77B7778-5C6B-4977-8240-5FD853999930	30857	1	NPR	8/27/2018
9028063A-CD2A-433A-894D-59C31AC08ECB	30856	1	NPR	8/24/2018
6972CF09-E0DE-480E-9E46-FADA858D93AE	30855	2	Pending	8/24/2018
F5A78617-8ACE-4567-911A-0C4B7655B981	30854	2	Pending	8/14/2018
B45C5624-7684-4920-828F-F0B26D836A42	30853	2	Pending	8/21/2018
99D616DF-9669-4935-8F14-DC526A018920	30852	1	NPR	8/21/2018
625812DC-213C-40A5-BAD4-73F39100101A	30851	1	NPR	8/20/2018
FB359575-B263-40C0-A676-1E7D3FFC6A98	30850	2	Pending	8/17/2018
2A9FEC75-36F8-4427-A095-3E27DFBD4460	30850	4	Approved	8/17/2018
1267DBF6-3407-48A4-81D1-8B478FDA1352	30850	1	NPR	8/17/2018
C74A5F2B-89BE-4DA7-8B35-34DFB490C0BD	30849	1	NPR	8/16/2018
689CA255-C12D-4544-BE0E-434E106CF8BA	30848	1	NPR	8/15/2018
85CF3C18-8416-4965-89D7-41408D744E77	30847	2	Pending	6/4/2018
9E470A27-BFA8-4328-B5B7-B23D42F7045B	30847	3	Revoked	6/4/2018
F192E52B-3BD2-4235-9EAB-0037A1D4DB0C	30846	1	NPR	8/14/2018
DEA8E0AF-12E0-497B-B4C1-7B23B753C4CD	30845	1	NPR	8/13/2018
CA8658F1-B920-4DF7-A767-4E1ED6B523C0	30844	1	NPR	8/13/2018
4AD011F6-AB44-483D-B229-E5B52EA9CEB6	30844	2	Pending	8/20/2018
AC467C0D-29F0-48E4-9643-AEEB44B3DFCC	30843	1	NPR	8/13/2018
7FAD5ACF-48D7-4716-B075-7144A5E7EB95	30842	1	NPR	8/10/2018
952634F1-FE04-47EA-AEA8-1A008300294C	30841	1	NPR	8/10/2018
8874316E-010A-41FA-B640-9D1F772A85AD	30840	1	NPR	8/9/2018
F4EFFC10-12AD-446C-B2CF-95B924E6EF22	30839	1	NPR	8/8/2018
E647FF95-BC5D-48E2-B9E0-0973555B9DEA	30838	1	NPR	8/7/2018
579E3F7A-3A99-45EC-997A-D0CBC6371E1E	30837	2	Pending	7/31/2018
AA8F9446-C353-4A0B-8464-4B0C64202070	30837	3	Revoked	7/31/2018
3070E688-1342-4385-89AB-368AB85A55CE	30837	3	Revoked	7/31/2018
F01A026D-12EB-47D7-82F9-21BFAE9403A6	30836	1	NPR	8/6/2018
29BB95E5-2AB8-4BF8-BD67-F980DEF7C976	30835	2	Pending	7/31/2018
7528793F-2405-408E-9A5F-0043A4F6C89D	30834	1	NPR	8/3/2018
991BF5DA-B1F7-4D1C-BF5C-50781FC8DE6D	30833	3	Revoked	8/3/2018
693B592C-BE95-429A-8BE6-4CBC2A6029E1	30833	2	Pending	8/3/2018
49265811-6DD3-4198-96AE-F870398A48DD	30832	2	Pending	8/3/2018
9E9072D3-6E59-4E78-BDD0-3EA40C6D2A34	30832	1	NPR	8/3/2018
1D56DFDF-65D7-4899-A56F-BCB4F42A0769	30832	3	Revoked	8/3/2018
1A8CBD02-B74F-4AF0-8BF7-B5A9F46A5730	30831	1	NPR	8/2/2018
4B952503-3E7F-4A2E-B912-DB7C7611CB70	30831	2	Pending	8/13/2018
8136E11E-0A6A-4AB9-B7EE-315B72A27280	30830	2	Pending	8/2/2018
F102367E-B10F-4D68-B1B9-AFA78550A8EF	30830	3	Revoked	8/2/2018
1FE23378-8104-4470-B4AA-54B3A41B3937	30829	1	NPR	8/2/2018
279CBF28-6FA7-494D-ADBB-52BC99039F43	30828	8	Withdrawn	8/13/2018
73C47F3D-A42A-40EF-BF64-A754984EB797	30828	2	Pending	7/31/2018
2F379890-5445-49C5-B3A7-0CDB8D35DC6A	30827	2	Pending	7/26/2018
FCAB40C2-EB84-4725-8D5A-E5CE69E07495	30827	8	Withdrawn	8/7/2018
21EED48F-41A7-4E8F-8CB9-43D9741EB2D0	30826	2	Pending	7/31/2018
15B36E65-1DE7-40C3-A206-F8C40206B83A	30826	8	Withdrawn	8/8/2018
674CAC9A-2EF6-44CC-810B-6BAD32FB1530	30825	3	Revoked	7/30/2018
CBC1C6BA-A5FD-47D4-8F2C-DD2710665771	30825	2	Pending	7/30/2018
9E045AED-F30C-4E25-816C-CA9C1D46EBE1	30824	1	NPR	7/30/2018
713186DB-AAC1-42CB-9412-F74AA6B98919	30823	3	Revoked	7/30/2018
00CBC9FA-19DC-4C6A-ACFE-641E7902744D	30823	2	Pending	7/30/2018
2A371FF5-A2CA-4932-91DF-CD8015FFD663	30822	1	NPR	7/30/2018
AE9C0286-2E07-4B9A-A388-9A193DCF5945	30821	1	NPR	7/27/2018
0D83D463-66DD-4D63-B071-4BB3EF520CCD	30820	2	Pending	7/25/2018
9D605033-5F61-43E8-B06B-E6343AD79F7C	30820	3	Revoked	7/25/2018
74D0876F-7C7D-46DF-9C69-644A8ADB8F7D	30819	3	Revoked	7/31/2018
912D1C43-1DE4-4126-8E1F-625F37D0090E	30819	1	NPR	7/25/2018
520FDDAB-99C8-4F50-ABBF-6CC711ACBFC4	30819	2	Pending	7/31/2018
EFBEA6FF-DBA2-4B1A-999F-FD3EB971A692	30818	1	NPR	7/25/2018
657682FE-C1D9-46A0-9A3B-B0037FA9A31B	30817	1	NPR	7/25/2018
51459FE3-D8E2-43A7-A639-8B99663B2DBA	30816	1	NPR	7/24/2018
C01629F4-9C31-4901-997B-A09A5911CC83	30815	1	NPR	7/23/2018
703E7437-B8D3-46C5-B9DE-C71C9CB4F110	30814	2	Pending	7/31/2018
D35ECEC8-E7FD-434F-831A-4CA1200D1D5A	30814	1	NPR	7/20/2018
7DAFD321-3488-4130-90B7-7397CB1D9DC0	30813	1	NPR	7/20/2018
E3080831-70B1-47F3-AFFB-4C4910246221	30812	4	Approved	7/17/2018
8E80156E-FA8F-4D0E-AA4A-CA1009F12D18	30812	2	Pending	7/17/2018
3AEC2595-1EA6-4B4F-B754-3F560D18540A	30811	1	NPR	7/18/2018
EA9F7D74-2ACA-432C-B0D9-ABD82943115F	30810	1	NPR	7/17/2018
B3BA8C5A-0125-4CFE-AEC5-7384A239D211	30809	2	Pending	7/17/2018
6D0BE560-7EC9-41A5-BEFC-3A2613F6B9B6	30809	1	NPR	7/17/2018
665A7ACD-D16E-4870-B35A-715AC8758E3A	30808	1	NPR	7/13/2018
E56E4B76-6003-4068-88F8-2D93F4B937F5	30807	1	NPR	7/13/2018
D13AD68D-C678-4050-AAC8-CB329D6F75BE	30806	2	Pending	7/13/2018
A29D7E38-8A1F-4549-887C-6C52A577F4DB	30806	3	Revoked	7/13/2018
CE7AEA71-C870-4307-A547-F4551FA8D4C0	30805	2	Pending	7/12/2018
3CA5AC83-B2A5-46A9-A8E3-862BC99E62E7	30805	3	Revoked	7/12/2018
49CEB7F1-64CC-4191-B2C8-B3F915851891	30804	1	NPR	7/11/2018
9E0BD9A9-4FE0-403E-A5F3-D439CF34171F	30803	1	NPR	7/11/2018
4C9DE898-7207-4670-AD54-DD8F0A1AC962	30802	2	Pending	7/12/2018
90C179D0-532D-488A-9FBB-AFF9E3F4046F	30802	1	NPR	7/11/2018
1D7B0535-9DF6-422C-AC12-E0BE4B687260	30801	3	Revoked	7/10/2018
B09855E2-3BE2-47E6-8616-0878FFE5D2A8	30801	2	Pending	7/10/2018
C9232188-198A-4E53-ABCA-D05B1EAEF28F	30800	1	NPR	7/10/2018
91A7BC9B-B109-4D51-8378-93E4F1396F27	30799	1	NPR	7/6/2018
AFF012A3-671D-4228-A51C-86E0CE28CEAB	30798	1	NPR	7/2/2018
F0EED6CC-70F7-487B-A681-DFE78936D3E3	30797	2	Pending	6/29/2018
40E411EA-FC4F-4787-A345-911C3960BD4D	30796	1	NPR	6/29/2018
09AF82AF-150E-4B9C-B051-B6059264CC33	30795	3	Revoked	6/29/2018
FBE277F5-1D2B-4C07-8533-12F01599A909	30795	2	Pending	6/29/2018
52A6979F-E57F-46DF-BB17-E1A1DF3370D8	30794	1	NPR	6/28/2018
3EA52A53-2320-4632-ABD7-EB57E9413F16	30793	1	NPR	6/28/2018
9A863F4C-ED8F-4804-AAD6-05904619927F	30792	1	NPR	6/27/2018
12A5A8D3-4A58-4B7D-A9CE-2BE0C60C970B	30791	8	Withdrawn	7/6/2018
E8A7943D-0DCC-44EC-865C-DB8B71978689	30791	2	Pending	6/26/2018
AACC133D-FAAE-43F6-A0A6-BB5304ED90D3	30790	1	NPR	6/25/2018
AC328FAC-09C8-4A49-9B2C-B11C7DB56185	30789	1	NPR	6/25/2018

Thanks

[Greg_Deckler]

Check out Page 11.

[sunny27]

Thanks for responding Greg, I think we are close.Below is the query i have.

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

[Greg_Deckler]

Well, i have generally found that getting to a correct answer usually involves divulging the entire scope of the issue.

[sunny27]

My explanation might be wrong before, so is there a solution for this?

Thanks