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 ...
Need Help
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.

     

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Hi ryard,

     

    If you also want to include same date of previous year in your calculation, you could use DATESBETWEEN() in your filter.

     

    CYS2 = calculate(

    sum(Table1[Quantity]),

    DATESBETWEEN(Tabl​e1[TheDate],

    FIRSTDATE(PARALLELPERIOD(Table1[TheDate],-1,YEAR)),

    LASTDATE(PARALLELPERIOD(Table1[TheDate],0,YEAR)),

    )

    )

     

    Reference:
    DATESBETWEEN Function (DAX)

     

    Regards,