Forum Discussion

Tiff's avatar
Tiff
Icon for Helper I rankHelper I
7 years ago
Solved

dax formulas through differents lines

Hi everyone ! 

 

May I implore ur help ? :o 

I'm on a probleme for days, I hope you can help me. 

 

I have two columns : 

 

01/01/2017 | 5

01/02/2017 | 7

01/03/2017 | 9

01/04/2017 | 6

01/05/2017 | 8

01/06/2017 | 2

01/07/2017 | 8

01/08/2017 | 6

01/09/2017 | 5

01/10/2017 | 2

01/11/2017 | 6

01/12/2017 | 9

01/01/2018 | 4

01/02/2018 | 4

01/03/2018 | 7

01/04/2018 | 8

01/05/2018 | 2

01/06/2018 | 8

01/07/2018 | 1

01/08/2018 | 8

01/09/2018 | 7

01/10/2018 | 4

01/11/2018 | 6

01/12/2018 | 3

 

One "Date" column, the other is "number" 
I'd like to do a cumulative sum. There, no problem with the formula in DAX : 

 

Cumule =
CALCULATE (
    SUM ( ESSAIS[QtyMonth] );
    FILTER (
        ALLEXCEPT ( ESSAIS; ESSAIS[Date].YEAR );
        ESSAIS[Date] <= EARLIER ( ESSAIS[Date] )
    )
)

 

But, I have an issue. I would like my sum to add the number on one rolling year. 
For example : 
[Jan2017+Feb2017+March2017+April2017+May2017+June2017+July2017+August2017+September2017+October2017+November2017+December2017]+(Jan2018-Jan2017)+(Feb2018-Jan2017-Feb2017)+(March2018-Jan2017-Feb2017-March2017) etc. 

I hope you understand what I mean. If not, don't hesitate to ask for more information ! 

Thank you so much ! 
Tiffaine 

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Tiff,

    Based on my test, you could refer to below formula:

    Column = var a=[Date]-365 
         var b=CALCULATE(SUM(Table1[Running total]),FILTER('Table1','Table1'[Date]=a))
         return [Running total]-b

    Result:

     

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

4 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Tiff,

    Based on my test, you could refer to below formula:

    Measure = CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2018))-
          11*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=1))-
          10*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=2))-
          9*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=3))-
          8*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=4))-
          7*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=5))-
          6*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=6))-
          5*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=7))-
          4*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=8))-
          3*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=9))-
          2*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=10))-
          1*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=11))

    Result:

     

     

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

    • Tiff's avatar
      Tiff
      Icon for Helper I rankHelper I
      7 years ago

      Thank you v-danhe-msft Daniel for your reply ! 

      I tried you formula, but it doesn't work :( 
      You can see below the column I want ("Cumulative Sum on rolling year"). 
      As you can see it, the first year (January 2017 to December 2017) is a simple cumulative sum. 
      Then, in January 2018, it keeps the simple cumulative sum, but remove the cumulative sum until January 2017. 
      In February 2018, it keeps the simple cumulative sum, but remove the cumulative sum until February 2017. 
      [...]
      In September 2018, it keeps the simple cumulative sum, but remove the cumulative sum until September 2017. 

      Do you know what I mean ? 
      I hope there is a way to do this... 

      Thanks a lot ! 
      Tiffaine 
       

      • v-danhe-msft's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Tiff,

        Based on my test, you could refer to below formula:

        Column = var a=[Date]-365 
         var b=CALCULATE(SUM(Table1[Running total]),FILTER('Table1','Table1'[Date]=a))
         return [Running total]-b

        Result:

         

        You could also download the pbix file to have a view.

         

        Regards,

        Daniel He