Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

partial month calculation

yearmonthamount   yearmonth amount
201711000   201711000
201723000   201722000
201737000 wanted result-> 201734000
2017410000   201743000
2017514000   201754000
2017618000   201764000
2017725000   201777000
2017830000   201785000
2017940000   2017910000
20171075000   20171035000
20171195000   20171120000
201712100000   2017125000
201812000   201812000
201823000   201821000
201835000   201832000
201847000   201842000
2018510000   201853000
2018612000   201862000
2018715000   201875000
2018819000   201884000
2018930000   2018911000
20181040000   20181010000
20181145000   2018115000
20181250000   2018125000

 


given the example on the right this is how my data are stored in table

on the left the result that i wanted :)

i need to calculate for each month in the year the partial (result= month amount- prev month amount) from jannuar to dicember and every year restart

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

    Hi Anonymous,

    Maybe you could try to modify the formula as below:

    Column =
VAR a = 'Table1'[month] - 1
RETURN
    [amount]
        - CALCULATE (
            SUM ( Table1[amount] ),
            FILTER (
                'Table1',
                'Table1'[month] = a
                    && 'Table1'[year] = EARLIER ( Table1[year] )
                    && 'Table1'[code] = EARLIER ( Table1[code] )
                    && 'Table1'[company] = "dk"
            )
        )

    Regards,

    Daniel He

9 Replies

  • Vishnu_1980's avatar
    Vishnu_1980
    Frequent Visitor
    7 years ago

    Hi Snifer,

     

    Do you want to get the partial results from the same month last year or

     

    you need to subtract previous month amount from YTD or MTD.

     

    Could please explain little more with two months data ? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Vishnu_1980

       

       

       

      20171075000   20171035000
      20171195000   20171120000
      201712100000   2017125000
      201812000   201812000
      201823000   201821000

      in the line we have for example

       

      11/2017  ->95000-75000=20000

      12/2017 -> 100000-95000=5000

      1/2018 -> 2000

      2/2018-> 3000-2000=1000

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

    Hi Anonymous,

    Based on my test, you could refer to below calculated column:

    Column = var a='Table1'[month]-1
return -(CALCULATE(SUM(Table1[amount]),FILTER('Table1','Table1'[month]=a&&'Table1'[year]=EARLIER(Table1[year])))-[amount])

    Result:

     

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

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-danhe-msft

       

      i miss to describe something important,

      the month is more than time repeted since is reffered to mukltiple account code

      so i need to make specific for each "code"

      yearmonthamountcode   yearmonth amountcode
      201711000101   201711000101
      201723000101   201722000101
      201737000101 wanted result-> 201734000101
      2017410000101   201743000101
      2017514000101   201754000101
      2017618000101   201764000101
      2017725000101   201777000101
      2017830000101   201785000101
      2017940000101   2017910000101
      20171075000101   20171035000101
      20171195000101   20171120000101
      201712100000101   2017125000101
      201812000101   201812000101
      201823000101   201821000101
      201835000101   201832000101
      201847000101   201842000101
      2018510000101   201853000101
      2018612000101   201862000101
      2018715000101   201875000101
      2018819000101   201884000101
      2018930000101   2018911000101
      20181040000101   20181010000101
      20181145000101   2018115000101
      20181250000101   2018125000101
      201711000102  201711000 102
      201723000102  201722000 102
      201737000102wanted result-> 201734000 102
      2017410000102  201743000 102
      2017514000102  201754000 102
      2017618000102  201764000 102
      2017725000102  201777000 102
      2017830000102  201785000 102
      2017940000102  2017910000 102
      20171075000102  20171035000 102
      20171195000102  20171120000 102
      201712100000102  2017125000 102
      201812000102  201812000 102
      201823000102  201821000 102
      201835000102  201832000 102
      201847000102  201842000 102
      2018510000102  201853000 102
      2018612000102  201862000 102
      2018715000102  201875000 102
      2018819000102  201884000 102
      2018930000102  2018911000 102
      20181040000102  20181010000 102
      20181145000102  2018115000 102
      20181250000102  2018125000 102
                 
      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous,

        You could modify my former formula as below:

        Column = var a='Table1'[month]-1
return [amount]-CALCULATE(SUM(Table1[amount]),FILTER('Table1','Table1'[month]=a&&'Table1'[year]=EARLIER(Table1[year])&&'Table1'[code]=EARLIER(Table1[code])))

        Result:

         

        Regards,

        Daniel He