Forum Discussion

user8289's avatar
user8289
New Member
2 years ago
Solved

How to create a running total column from a certain date range in DAX

I am trying to create a running total of a list of data.  I have data from a large date range but only want to create the running total over the last 7 days and next 7 days input: date             ...
Need Help
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, user8289 

     

    Replace the formula with the following:

    Running total = 
Var _Sum=CALCULATE( SUM('table'[size]),
  FILTER(ALL('table'), 'table'[Date] <= SELECTEDVALUE('table'[date])
 &&[Date]>=MIN('Date'[Date])&&[Date]<=MAX('Date'[Date]
)))
Return
IF(SELECTEDVALUE('Table'[Date])>=MIN('Date'[Date])&&SELECTEDVALUE('Table'[Date])<=MAX('Date'[Date]),_Sum,BLANK())

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
2 years ago

Hi, user8289 

 

You can try the following methods.
Create a new date table.

Measure:

Running total = 
Var _Sum=CALCULATE( SUM('table'[size]),
  FILTER(ALL('table'[Date]), 'table'[Date] <= max('table'[date])
                    &&[Date]>=MIN('Date'[Date])&&[Date]<=MAX('Date'[Date]
)))
Return
IF(SELECTEDVALUE('Table'[Date])>=MIN('Date'[Date])&&SELECTEDVALUE('Table'[Date])<=MAX('Date'[Date]),_Sum,BLANK())

Is this the result you expect? Please see the attached document.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • user8289's avatar
    user8289
    New Member
    2 years ago

    Thanks for your reply and sample data. However this did not work for me. Your example seeems to have the required functionality however when applied to my data it gives the result below:

     

     

    • v-zhangti's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      2 years ago

      Hi, user8289 

       

      Replace the formula with the following:

      Running total = 
Var _Sum=CALCULATE( SUM('table'[size]),
  FILTER(ALL('table'), 'table'[Date] <= SELECTEDVALUE('table'[date])
 &&[Date]>=MIN('Date'[Date])&&[Date]<=MAX('Date'[Date]
)))
Return
IF(SELECTEDVALUE('Table'[Date])>=MIN('Date'[Date])&&SELECTEDVALUE('Table'[Date])<=MAX('Date'[Date]),_Sum,BLANK())

      Is this the result you expect? Please see the attached document.

       

      Best Regards,

      Community Support Team _Charlotte

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • user8289's avatar
        user8289
        New Member
        2 years ago

        You are awsome!! Thank you thats exactly what I wanted.....