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.
