Forum Discussion

ryard's avatar
ryard
New Member
10 years ago

DAX - Running 365 Day Total

I have read through many posts on how to create a running total in DAX and thought I had the solution.  To test my knowledge I manually created a table that contained two columns, a date column, and a quantity column.  I used the formula below to create the running total measure.  When I tested the measure, the running total stopped at the year boundary.  Thoughts?

 

Bob

 

Measure:

 

CYS2 = calculate(sum(Table1[Quantity]),DATESINPERIOD(Table1[TheDate],lastdate(Table1[TheDate]),-365,day))

 

Data/Results:

 

 

Need Help

4 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    10 years ago

    ryard Results seem correct to me? What do you expect the results to be?

     

    • ryard's avatar
      ryard
      New Member
      10 years ago

      I expected the total for a date to be the sum of all Quantities for the previous 365 days.  So for the row with 2/15/16 as TheDate I expected the sum of all quantities between 02/15/15 through 02/15/16 which would include the following:

       

      02/15/15 - 10

      03/02/15 - 10

      03/15/15 - 10

      01/01/16 - 1

      01/15/16 - 1

      02/01/16 - 1

      02/15/16 - 1

       

      For a total of 34.

       

      Bob

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        10 years ago

        When you subtract 365 days - you do end up at 2/15 again (if not a Leap Year)

         

        However you don't include that date in your calculation.