cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brookied Frequent Visitor
Frequent Visitor

Subtracting a once off value from a monthly total

Hi Guys

this is driving me nuts. 

 

I have a a volume of income data. I have summerized this data per month on a calculated colunm

 

so in effect i have 

 

Month   ¦  Income  ¦ Tax  Ref ¦  New Colum

Jan         ¦ 125648  ¦              ¦   125648  

Feb        ¦ 125463   ¦ 256421 ¦  -130958

Mar       ¦ 586458   ¦              ¦  586458

Apr        ¦ 568412   ¦              ¦  568412

 

What i need to do is take away a set once off  value from Feb only -  but leave all other values the same as uneffected, then i need to do a cumulative sum on the months which i have worked out. 

 

This is needed to take away a tax refund in Feb that has already been allocated back to 2016 and  so should not effect 2017 income but i need to reference it.  

 

Any help would be great as i have gotten close but keep falling at the last hurdle

2 REPLIES 2
Moderator v-caliao-msft
Moderator

Re: Subtracting a once off value from a monthly total

@Brookied,

 

Not sure if I understand your requirement correctly. You can get a table like below

Month   ¦  Income  ¦ Tax  Ref ¦

Jan         ¦ 125648  ¦              ¦ 

Feb        ¦ 125463   ¦ 256421 ¦ 

Mar       ¦ 586458   ¦              ¦

Apr        ¦ 568412   ¦              ¦

And you want a calculated column in your original table

Month   ¦  Income  ¦ Tax  Ref ¦  New Colum

Jan         ¦ 125648  ¦              ¦   125648  

Feb        ¦ 125463   ¦ 256421 ¦  -130958

Mar       ¦ 586458   ¦              ¦  586458

Apr        ¦ 568412   ¦              ¦  568412

 

You can create a calculated column using the DAX below
New Column  =  table[Income] - table[Tax Ref]

 

If I have anything misunderstood, please point it out and provide us more detail information, so that we can make further analysis.

 

Regards,

Charlie Liao

Brookied Frequent Visitor
Frequent Visitor

Re: Subtracting a once off value from a monthly total

Hi Not quite mate but found a different solution, I removed the income figure from the source by using the edit query, finding the cell with the offending value and replacing with a zero. 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,788)