Ignore values less than zero in calculation

[D_Eichenberger]

Hi

I just started with Power-BI and I am struggeling with something I could solve in Excel in notime -.-

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".

My formula so far:

Act_ATSys_CummOI = CALCULATE (
                                SUM (Actual_Calc[Act_ATSys_OI]) ; (Actual_Calc[Act_ATSys_OI]) >0;
                                FILTER ( ALL (Actual_Calc); Actual_Calc[Act_Week] <= EARLIER (Actual_Calc[Act_Week]))
                                )

I tried to exclude them with an IF statment, but I faild. Any help would be highly appreciated!

[v-danhe-msft · Accepted solution]

Hi @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]<>0,CALCULATE(SUM(Sheet1[Act_ATSys_OI]),

FILTER(ALL(Sheet1),'Sheet1'[Act_week]<=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.pb...

Regards,

Daniel He

[D_Eichenberger]

You made my day! Thanks a lot, works like a charm.