Forum Discussion

bjoshi's avatar
bjoshi
Icon for Resolver I rankResolver I
9 years ago
Solved

Shifting down Cumulative Total by one row

I have created a calculated column 'CumulativeTotalColumn' using this dax formula:

 

CumulativeTotalColumn = 
var rowdate = Journals[JournalDate] 
Return
   CALCULATE(
    SUM(Journals[Total]),
     FILTER(
        'Journals',
        'Journals'[JournalDate]<= rowdate)
     )

 The result:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now, I need another calculated column, 'CalculatedTotalColumnShifted' that shifts the 'CumulativeTotalColumn'  down by one row. i.e. 1st March 2016 has the CumulativeTotal of 29 Feb, 2nd March has the Cumulative Total of 1st March and so on.

 

Any ideas?

 

Thank You

  • bjoshi

     

    Hi, you can obtain with this

     

    CumulativeTotalColumnShifted = 
    var rowdate = Journals[JournalDate] 
    Return
       CALCULATE(
        SUM(Journals[Total]),
         FILTER(
            'Journals',
            'Journals'[JournalDate]< rowdate)
         )

     

     

2 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion

    bjoshi

     

    Hi, you can obtain with this

     

    CumulativeTotalColumnShifted = 
    var rowdate = Journals[JournalDate] 
    Return
       CALCULATE(
        SUM(Journals[Total]),
         FILTER(
            'Journals',
            'Journals'[JournalDate]< rowdate)
         )

     

     

    • bjoshi's avatar
      bjoshi
      Icon for Resolver I rankResolver I

      Vvelarde. Wow, I didn't realize that. I am very new to DAX.  But, glad that the community members are very helpful.