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

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