Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

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




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.



Charlie Liao

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors