Reply
JCon
Frequent Visitor

Running Balance Help please

 

  • 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
          StatusactualclosedateestimatedclosedateCreated_dateValue
          Lost13/02/202031/03/202017/11/20191 300 000.0
          Lost31/07/201931/07/201922/10/201946 137 000.0
          Lost18/11/201909/11/201925/10/2019164 677.1
          Gained19/09/201919/09/201919/09/20194 000 000.0
          Lost19/02/202126/02/202122/01/20200
          Lost09/09/202031/08/202017/01/2020600 000.0
          Lost24/01/202031/01/202019/11/20193 500 000.0
          Lost17/10/201910/10/201917/10/20198 000 000.0
          Lost31/07/201931/07/201917/10/2019350 000 000.0
          Open 06/11/202023/10/20192 875 218.8
          Lost28/10/202231/12/202123/10/201949 226 906.7
          Lost09/06/202131/12/202023/10/201910 638 427.5
          Gained23/10/201931/10/201923/10/20195 410 649.7
          Lost13/10/202301/08/202023/10/201916 546 523.8
          Gained31/10/201931/10/201923/10/20194 923 482.1
          Gained23/10/201923/10/201923/10/2019102 572 543.1
          Gained19/01/201819/01/201819/01/201823 000 000.0
          Lost26/03/201901/08/201816/02/20181 560 000.0
          Lost26/03/201909/11/201816/02/2018500 000.0


          Any help would be appreciated.
3 REPLIES 3
lbendlin
Super User
Super User

What is your locale? That currency format looks interesting.

JCon
Frequent Visitor

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

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)