Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

partial month calculation

year month amount       year month  amount 2017 1 1000       2017 1 1000 2017 2 3000       2017 2 2000 2017 3 7000   wanted result->   2017 3 4000 2017 ...
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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      Column = var a='UploadAccounts'[Month]-1
      return [-amount]-CALCULATE(SUM(UploadAccounts[-amount]),FILTER('UploadAccounts','UploadAccounts'[Month]=a&&'UploadAccounts'[Year]=EARLIER(UploadAccounts[Year])&&'UploadAccounts'[AccountCode]=EARLIER(UploadAccounts[AccountCode])))
       
      code so applied its not working, it give as output strange result
       
       
      basically moltiply prev month
       
      example jannuar
       
      1000
      it turn february 2000
      it doest make sum february-januar
      just moltiply 2
      check the screen
       
      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous,

        Could you have downloaded my pbix file to have a view? All the data I used is given from your last post, if it is still incorrect, could you please upload your pbix file to OneDrive or Dropbox and send the link here to let me have a test?

         

        Regards,

        Daniel He