Running Balance Help please
Hello, I need assitance creating a running balance in Dax for specific dates as conditions. Viewpoint determined by an on page filter where you select the month end you want to view the data from. For an example use July 2023 Totals using Status, Created Date, Estimated Close date and Closed Date If Status = Open and Created date is within the last 12 months of July 2023 or before: Est close date determines where it should be shown in the next 12month forecast, included in all Open Totals If Status = Open and Created date is after July 2023 Do not include in Open totals If status = closed and actual close date is within the last 12months of July 2023: In the 12 months prior to July 2023, but not this fiscal year - it will show in any 12month closed total in the current fiscal year until end of July 2023- it will show in any 12month closed total and any YTD closed total if Status = closed and actual close date is after July 2023 and created on date is before July 2023 include in open totals Sample of data Status actualclosedate estimatedclosedate Created_date Value Lost 13/02/2020 31/03/2020 17/11/2019 1 300 000.0 Lost 31/07/2019 31/07/2019 22/10/2019 46 137 000.0 Lost 18/11/2019 09/11/2019 25/10/2019 164 677.1 Gained 19/09/2019 19/09/2019 19/09/2019 4 000 000.0 Lost 19/02/2021 26/02/2021 22/01/2020 0 Lost 09/09/2020 31/08/2020 17/01/2020 600 000.0 Lost 24/01/2020 31/01/2020 19/11/2019 3 500 000.0 Lost 17/10/2019 10/10/2019 17/10/2019 8 000 000.0 Lost 31/07/2019 31/07/2019 17/10/2019 350 000 000.0 Open 06/11/2020 23/10/2019 2 875 218.8 Lost 28/10/2022 31/12/2021 23/10/2019 49 226 906.7 Lost 09/06/2021 31/12/2020 23/10/2019 10 638 427.5 Gained 23/10/2019 31/10/2019 23/10/2019 5 410 649.7 Lost 13/10/2023 01/08/2020 23/10/2019 16 546 523.8 Gained 31/10/2019 31/10/2019 23/10/2019 4 923 482.1 Gained 23/10/2019 23/10/2019 23/10/2019 102 572 543.1 Gained 19/01/2018 19/01/2018 19/01/2018 23 000 000.0 Lost 26/03/2019 01/08/2018 16/02/2018 1 560 000.0 Lost 26/03/2019 09/11/2018 16/02/2018 500 000.0 Any help would be appreciated.
Dax measures for running balances
Hello, I need assitance creating a running balance in Dax for specific dates as conditions. Viewpoint determined by an on page filter where you select the month end you want to view the data from. For an example use July 2023 Totals using Status, Created Date, Estimated Close date and Closed Date If Status = Open and Created date is within the last 12 months of July 2023 or before: Est close date determines where it should be shown in the next 12month forecast, included in all Open Totals If Status = Open and Created date is after July 2023 Do not include in Open totals If status = closed and actual close date is within the last 12months of July 2023: In the 12 months prior to July 2023, but not this fiscal year - it will show in any 12month closed total in the current fiscal year until end of July 2023- it will show in any 12month closed total and any YTD closed total if Status = closed and actual close date is after July 2023 and created on date is before July 2023 include in open totals Sample of data Status actualclosedate estimatedclosedate Created_date Value Lost 13/02/2020 31/03/2020 17/11/2019 1 300 000.0 Lost 31/07/2019 31/07/2019 22/10/2019 46 137 000.0 Lost 18/11/2019 09/11/2019 25/10/2019 164 677.1 Gained 19/09/2019 19/09/2019 19/09/2019 4 000 000.0 Lost 19/02/2021 26/02/2021 22/01/2020 0 Lost 09/09/2020 31/08/2020 17/01/2020 600 000.0 Lost 24/01/2020 31/01/2020 19/11/2019 3 500 000.0 Lost 17/10/2019 10/10/2019 17/10/2019 8 000 000.0 Lost 31/07/2019 31/07/2019 17/10/2019 350 000 000.0 Open 06/11/2020 23/10/2019 2 875 218.8 Lost 28/10/2022 31/12/2021 23/10/2019 49 226 906.7 Lost 09/06/2021 31/12/2020 23/10/2019 10 638 427.5 Gained 23/10/2019 31/10/2019 23/10/2019 5 410 649.7 Lost 13/10/2023 01/08/2020 23/10/2019 16 546 523.8 Gained 31/10/2019 31/10/2019 23/10/2019 4 923 482.1 Gained 23/10/2019 23/10/2019 23/10/2019 102 572 543.1 Gained 19/01/2018 19/01/2018 19/01/2018 23 000 000.0 Lost 26/03/2019 01/08/2018 16/02/2018 1 560 000.0 Lost 26/03/2019 09/11/2018 16/02/2018 500 000.0 Any help would be appreciated.
Running Balance and Adding Previous Month Balance
I have calculated my running balance: if balance between available and scheduled is less than 0, than 0 otherwise the difference of available to scheduled - we can't carry over a positve balance. If you don't use it you lose it. My problem is that I then need adjusted scheduled hours to be scheduled hours + -running balance from previous month. You can see in my formula I used CALCULATE('Scheduled Hours Report'[Running Balance],PREVIOUSMONTH('Date'[Date])) but it's not pulling my running balance from the previous month. Is this because my running balance is from two different tables linked by date? If so, how do I get an adjusted scheduled hours column based on any negative difference between available and scheduled hours.
