Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
snifer
Post Patron
Post Patron

partial month calculation

‎01-13-2019 02:58 AM
yearmonthamount   yearmonth amount
201711000   201711000
201723000   201722000
201737000 wanted result-> 201734000
2017410000   201743000
2017514000   201754000
2017618000   201764000
2017725000   201777000
2017830000   201785000
2017940000   2017910000
20171075000   20171035000
20171195000   20171120000
201712100000   2017125000
201812000   201812000
201823000   201821000
201835000   201832000
201847000   201842000
2018510000   201853000
2018612000   201862000
2018715000   201875000
2018819000   201884000
2018930000   2018911000
20181040000   20181010000
20181145000   2018115000
20181250000   2018125000

 


given the example on the right this is how my data are stored in table

on the left the result that i wanted 🙂

i need to calculate for each month in the year the partial (result= month amount- prev month amount) from jannuar to dicember and every year restart

Labels:
Message 1 of 10
1,033 Views
0
1 ACCEPTED SOLUTION
v-danhe-msft
Microsoft Employee
Microsoft Employee
In response to snifer

‎01-15-2019 01:42 AM

Hi @snifer,

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 10 of 10
963 Views
0
9 REPLIES 9
v-danhe-msft
Microsoft Employee
Microsoft Employee

‎01-13-2019 11:52 PM

Hi @snifer,

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:

1.PNG

 

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
partial month calculation.pbix
Message 4 of 10
1,001 Views
0
snifer
Post Patron
Post Patron
In response to v-danhe-msft

‎01-14-2019 12:47 AM

@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
           
Message 5 of 10
997 Views
0
v-danhe-msft
Microsoft Employee
Microsoft Employee
In response to snifer

‎01-14-2019 12:59 AM

Hi @snifer,

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:

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
partial month calculation.pbix
Message 6 of 10
992 Views
0
snifer
Post Patron
Post Patron
In response to v-danhe-msft

‎01-14-2019 01:10 AM
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
 
Message 7 of 10
987 Views
0
v-danhe-msft
Microsoft Employee
Microsoft Employee
In response to snifer

‎01-14-2019 01:15 AM

Hi @snifer,

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 8 of 10
981 Views
0
snifer
Post Patron
Post Patron
In response to v-danhe-msft

‎01-15-2019 12:10 AM

@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
Message 9 of 10
969 Views
0
v-danhe-msft
Microsoft Employee
Microsoft Employee
In response to snifer

‎01-15-2019 01:42 AM

Hi @snifer,

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 10 of 10
964 Views
0
Vishnu_1980
Frequent Visitor

‎01-13-2019 03:22 AM

Hi Snifer,

 

Do you want to get the partial results from the same month last year or

 

you need to subtract previous month amount from YTD or MTD.

 

Could please explain little more with two months data ? 

Message 2 of 10
1,027 Views
0
snifer
Post Patron
Post Patron
In response to Vishnu_1980

‎01-13-2019 03:27 AM

@Vishnu_1980

 

 

 

20171075000   20171035000
20171195000   20171120000
201712100000   2017125000
201812000   201812000
201823000   201821000

in the line we have for example

 

11/2017  ->95000-75000=20000

12/2017 -> 100000-95000=5000

1/2018 -> 2000

2/2018-> 3000-2000=1000

Message 3 of 10
1,024 Views
0

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

View All