Forum Discussion

SparkD's avatar
SparkD
New Member
1 year ago
Solved

Running Total Keeps resetting

Im currently using the following DAX:

 

Running Sum = CALCULATE([Planned Count],DATESYTD('Month Ending'[Month Ending]))
 
Problem is it calculates fine within one year, but resets every new calendar year ?
I dont want this to happen.  Ideas of how to overcome this please.
 

 

 
 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from bhanu_gautam and PBIViz_2024.

     

    Hi SparkD ,

     

    If you want to achieve the effect that the months beyond the current month are blank, a measure can be written for filtering.

     

    Here is a simple example:

    1.Create a table:

    2.Create a measure:

     

    Measure = IF(MAX('Table'[Month])<=MONTH(TODAY()),1,0)

     

    3.Drag the measure to the filter pane, then set the condition to 1:

    4.The effect is as follows:

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

     

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    SparkD , Try below measure

     

    DAX
    Running Sum =
    CALCULATE(
    [Planned Count],
    FILTER(
    ALL('Month Ending'),
    'Month Ending'[Month Ending] <= MAX('Month Ending'[Month Ending])
    )
    )

    • SparkD's avatar
      SparkD
      New Member
      1 year ago

      Thats great and works however I note that the "Running Sum" called Completed RT stays at the same level even for months greater than todays date.  i.e. the current month is Sept 2024 where the completed running totday is 55, but the same value is showing for all months until financial month end.     Any way I can amend the DAX so that the months beyond the current month are blank ???

       

       

      Current DAX for CompletedRT is:    

       

      CompletedRT =
      VAR MaxDate = MAX ( 'Month Ending'[Month Ending] ) -- Saves the last visible date
      RETURN
          CALCULATE (
              [Completed Count],            -- Computes completed
              'Month Ending'[Month Ending] <= date(2024,09,30) ,   -- Where date is before the last visible date
              DATESBETWEEN('Month Ending'[Month Ending],date(2024,04,01),max('Month Ending'[Month Ending])))
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Thanks for the reply from bhanu_gautam and PBIViz_2024.

         

        Hi SparkD ,

         

        If you want to achieve the effect that the months beyond the current month are blank, a measure can be written for filtering.

         

        Here is a simple example:

        1.Create a table:

        2.Create a measure:

         

        Measure = IF(MAX('Table'[Month])<=MONTH(TODAY()),1,0)

         

        3.Drag the measure to the filter pane, then set the condition to 1:

        4.The effect is as follows:

        Best Regards,
        Zhu
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

         

         

  • PBIViz_2024's avatar
    PBIViz_2024
    Advocate I
    1 year ago

    Hi,

     This is because you have used DATESYTD.since you have not provided 2nd parameter which is year end date.it will use 31st December as the year end and calculate the running total for the Calendar Year.If you need this for the entire fiscal Year you have to specify the Year end.

    if your Fiscal year is April-March.Dax should be something like below

    Running Sum = CALCULATE([Planned Count],DATESYTD('Month Ending'[Month Ending],"31/03"))

    More info.

    https://learn.microsoft.com/en-us/dax/datesytd-function-dax

     

    Regards,