Ignore values less than zero in calculation

Question

HiI just started with Power-BI and I am struggeling with something I could solve in Excel in notime -.-&nbsp;I have a colum (Act_ATSys_OI) where the weekly bookings get calculated from another table and another one (Act_ATSys_CummOI) where I want to summarize them as a YTD figure. Now I managed to do that, but I do not want the Formula in column "Act_ATSys_CummOI" to extend further than there is a value above 0 in column "Act_ATSys_OI". - See screenshot below where the last cells constantly show "8'846".&nbsp;My formula so far:&nbsp;Act_ATSys_CummOI = CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM (Actual_Calc[Act_ATSys_OI]) ; (Actual_Calc[Act_ATSys_OI]) &gt;0;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( ALL (Actual_Calc); Actual_Calc[Act_Week] &lt;= EARLIER (Actual_Calc[Act_Week]))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;I tried to exclude them with an IF statment, but I faild. Any help would be highly appreciated!&nbsp;

v-danhe-msft · Accepted Answer

Hi&nbsp;D_Eichenberger,Based on my test, you can refer to below steps:You can create a column to calculate your value and you can see the result.Total running = IF([Act_ATSys_OI]&lt;&gt;0,CALCULATE(SUM(Sheet1[Act_ATSys_OI]),FILTER(ALL(Sheet1),'Sheet1'[Act_week]&lt;=EARLIER(Sheet1[Act_week]))))You can also download the PBIX file to have a view.https://www.dropbox.com/s/5pb0de4u4emodqa/Ignore%20values%20less%20than%20zero%20in%20calculation.pbix?dl=0&nbsp;Regards,Daniel He

Forum Discussion

Ignore values less than zero in calculation

2 Replies

Related content

DAX for calculating retroactive values

Calculate and ignoring null value

IGNORE NEGATIVE VALUES

Ignore relationship in CALCULATE

Calculating Average while ignoring zero/null values