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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        v-danhe-msft

        maybe i find out why is not working in the table are many company with same month code year so maybe addiing a condition for same compnay will fix everything .. how i can add this contition?

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