Question on Beginning Balance

[Razorbx13]

I have a "beginning balance" question.  I have several records by employee of vacation and sick hours accrued and taken, with a pay date associated with each.  So these dates run from 2017 through today.  What I need to do is create a Beginning Balance based on the net of trancations, then columns that show Accrued Hours and Hours Taken for a period of time, and then the Net.  Something like the below.  I want the user to be able to select a range of dates and have that total the Accrued and Taken, then have it calculate the net of Accrued and Taken before the range of dates to give the Beginning balance.  So say they select 1/1/2019 to 12/31/2019, the Accured and Taken are the Sum of those hours for 2019, but the Beginning Balance is the NET of every entry prior to 1/1/2019.  

Thanks in advance!

Name	Beginning Balance	Accrued	Taken	Net (Beginning Balance+Accrued-Taken
John Doe	123	80	23	180

[Anonymous]

Please send pbix file.

[lbendlin]

This is how your fact table would need to look like to make that happen:

Name  Date  Amount  Transaction_Type

The Transction_Type column is optional for what you want to achieve.

Does the above match your situation?

[Razorbx13]

Basically wondering why I am seeing the beginning balance as noted below, not what should be coming as a summary for each year.  BTW - The underlying data is a fact table and this data is test data.  I want to ensure it is showing beginning balance for each leave code.

[v-kelly-msft]

Hi  @Razorbx13 ,

Could you pls upload your .pbix file to Onedrive business and share the link with us ?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Razorbx13 · Accepted solution]

I got it resolved!

[Razorbx13]

Well I have a fact table with transactions that contain name, leave type, date and hours.  Each row is an accrual or leave taken.  It is joined to a dates table.  Basically want rows similar to what I show where I have name, leave type, then YEAR.  The accrual hours and actual leave hours are grouped by year.  Just want a beginning balance for each Row where year is present, and for each leave type.  

John Doe, sick, 2016, beginning bal (which would be zero), accrual, actual, net remaining

john doe, sick, 2017, beginning balance (ending 2016 bal), accrual, actual, net remaining

john doe, sick, 2018......

and then John Doe with the next leave type.