Forum Discussion

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 ...

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

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

partial month calculation.pbix30 KB

Anonymous

Not applicable

7 years ago

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"

year	month	amount	code				year	month	amount	code
2017	1	1000	101				2017	1	1000	101
2017	2	3000	101				2017	2	2000	101
2017	3	7000	101		wanted result->		2017	3	4000	101
2017	4	10000	101				2017	4	3000	101
2017	5	14000	101				2017	5	4000	101
2017	6	18000	101				2017	6	4000	101
2017	7	25000	101				2017	7	7000	101
2017	8	30000	101				2017	8	5000	101
2017	9	40000	101				2017	9	10000	101
2017	10	75000	101				2017	10	35000	101
2017	11	95000	101				2017	11	20000	101
2017	12	100000	101				2017	12	5000	101
2018	1	2000	101				2018	1	2000	101
2018	2	3000	101				2018	2	1000	101
2018	3	5000	101				2018	3	2000	101
2018	4	7000	101				2018	4	2000	101
2018	5	10000	101				2018	5	3000	101
2018	6	12000	101				2018	6	2000	101
2018	7	15000	101				2018	7	5000	101
2018	8	19000	101				2018	8	4000	101
2018	9	30000	101				2018	9	11000	101
2018	10	40000	101				2018	10	10000	101
2018	11	45000	101				2018	11	5000	101
2018	12	50000	101				2018	12	5000	101
2017	1	1000	102			2017	1	1000		102
2017	2	3000	102			2017	2	2000		102
2017	3	7000	102	wanted result->		2017	3	4000		102
2017	4	10000	102			2017	4	3000		102
2017	5	14000	102			2017	5	4000		102
2017	6	18000	102			2017	6	4000		102
2017	7	25000	102			2017	7	7000		102
2017	8	30000	102			2017	8	5000		102
2017	9	40000	102			2017	9	10000		102
2017	10	75000	102			2017	10	35000		102
2017	11	95000	102			2017	11	20000		102
2017	12	100000	102			2017	12	5000		102
2018	1	2000	102			2018	1	2000		102
2018	2	3000	102			2018	2	1000		102
2018	3	5000	102			2018	3	2000		102
2018	4	7000	102			2018	4	2000		102
2018	5	10000	102			2018	5	3000		102
2018	6	12000	102			2018	6	2000		102
2018	7	15000	102			2018	7	5000		102
2018	8	19000	102			2018	8	4000		102
2018	9	30000	102			2018	9	11000		102
2018	10	40000	102			2018	10	10000		102
2018	11	45000	102			2018	11	5000		102
2018	12	50000	102			2018	12	5000		102

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
partial month calculation.pbix37 KB
- 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
  v-danhe-msft
  
  example jannuar
  
  1000
  it turn february 2000
  it doest make sum february-januar
  just moltiply 2
  check the screen
  https://imgur.com/EJRXYj5