dax formulas through differents lines

[Tiff]

Hi everyone ! 

May I implore ur help ? 😮 

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 

[v-danhe-msft]

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]

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 · Accepted solution]

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

[Tiff]

Thanks a lot ! It works ! 

I was beggining to think this wasn't possible in Power BI 🙂 

Thank you again ! 
Tiffaine