Forum Discussion

Tiff's avatar
Tiff
Helper 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...
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

v-danhe-msft's avatar
v-danhe-msft
Microsoft 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
Helper 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
    Microsoft 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

    • Tiff's avatar
      Tiff
      Helper I
      7 years ago

      Thanks a lot ! It works ! 

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

      Thank you again ! 
      Tiffaine