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,

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