Beginning and Ending balance for every month

[PBI5851]

Hello. For the below raw data I am trying to maintain a waterfall calculation by providing a beginning and ending balance for month, quarter and year. But not sure how to accomplish it, please can you advise. 

Raw data

PersonID	IncomingDate	EndDate
John1	1/10/2021
John2	2/10/2021	2/20/2022
John3	2/25/2021	3/15/2021
John4	3/5/2021	7/10/2021
John5	4/5/2021	4/20/2021
John6	5/5/2021
John7	5/8/2021
John8	5/9/2021	5/12/2021
John9	6/10/2021	6/10/2021
John10	8/10/2021	9/12/2021
John11	9/10/2021
John12	11/12/2021	11/5/2021
John13	12/15/2021	12/30/2021
John14	1/20/2022	1/20/2022
John15	1/25/2022	2/10/2022
John16	2/10/2022
John17	2/12/2022	2/15/2022
John18	2/16/2022	2/20/2022
John19	2/20/2022

Expected Monthly crosstab

	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2022	2022
	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb
Begin balance	0	1	3	3	3	5	5	4	5	5	5	5	5	6
Incoming	1	2	1	1	3	1	0	1	1	0	1	1	2	4
Cancelled	0	0	1	1	1	1	1	0	1	0	1	1	1	4
End Balance	1	3	3	3	5	5	4	5	5	5	5	5	6	6

Expected Quarterly tab

	2021	2021	2021	2021	2022
	Q1	Q2	Q3	Q4	Q1
Begin balance	0	3	5	5	5
Incoming	4	5	2	2	6
Cancelled	1	3	2	2	5
End Balance	3	5	5	5	6

Expected Yearly tab

	2021	2022
Begin balance	0	5
Incoming	13	6
Cancelled	8	5
End Balance	5	6

any recommendations please. 

[PBI5851]

found the solution.. 

SWITCH(TRUE(),

ISFILTERED(Datetable[Year Month]),LASTDATE(PREVIOUSMONTH(Datetable[Date])),

ISFILTERED(Datetable[Year Qtr]),LASTDATE(PREVIOUSQUARTER(Datetable[Date])),

ISFILTERED(Datetable[Year]),LASTDATE(PREVIOUSYEAR(Datetable[Date])))

[PBI5851]

@V-lianl-msft The switch statment is capturing incorrect month end values as below .

For Feb 2019, its shows 1/28, rather than 1/31 and so on. Please advise. 

[V-lianl-msft · Accepted solution]

Hi @PBI5851 ,

Based on your description, I created a sample pbix for your reference:

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