Running Balance Help please

[JCon]

• Hello,

  I need assitance creating a running balance in Dax for specific dates as conditions.
  
  

  1. 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
  2. Totals using  Status, Created Date, Estimated Close date and Closed Date
  1. If Status = Open and Created date is within the last 12 months of July 2023 or before:
  1. Est close date determines where it should be shown in the next 12month forecast, included in all Open Totals
     
  2. If Status = Open and Created date is after July 2023
  1. Do not include in Open totals
     
  3. If status = closed and actual close date is within the last 12months of July 2023:
  1. In the 12 months prior to July 2023, but not this fiscal year - it will show in any 12month closed total
  2. in the current fiscal year until end of July 2023- it will show in any 12month closed total and any YTD closed total
     
  4. if Status = closed and actual close date is after July 2023 and created on date is before July 2023
  1. 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.

[lbendlin]

What is your locale? That currency format looks interesting.

[JCon]

Hi @lbendlin not sure what locale is set to, but just inserted some made up data for assistance with the DAX measure.

[lbendlin]

Let me rephrase. In what country are spaces used as thousand separator?