POWER BI If the query return 0 change the previous value

[viktorastzallas]

I have this table and from the money table i want to cut the money per month , each month is 260 but if the amount is less than 20 i want the value to be 20 and then previous value -20 . Any idea how to do it?

[v-chenwuz-msft]

Hi @viktorastzallas ,

Is this you want?

Please try this dax to do that:

Column = 
var _a = 'Table'[acount]
VAR _L_20 = FILTER('Table',[acount]=EARLIER('Table'[acount])&&[value]<20)
var _COUNT_L_20 = COUNTROWS(_L_20)
var _SUM_L_20 = SUMX(_L_20,[value])
var _ADD1 = ADDCOLUMNS(_L_20,"A",MAXX(TOPN(1,FILTER('Table',[acount]=_a&&[value]>=20&&[INDEX]<EARLIER('Table'[INDEX])),[INDEX],DESC),[INDEX]))
VAR _DIS =SUMMARIZE(_ADD1,[A])
VAR _NUM= _COUNT_L_20*20 -_SUM_L_20
RETURN
IF([value]<20,20,IF(CONTAINSROW(_DIS,[INDEX]),[value]-_NUM,[value]))

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[viktorastzallas]

260  * DATEDIFF('TABLE'[START],'TABLE'[END]+1,DAY)/DAY( EOMONTH( [END], 0 ) ) . This is the type i use to calculate the money per month(260 FOR A FULL MONTH WITH 30 OR 31 DAYS) . The total money for each account is 1040 and must not be exceeded . But when i use this type if there is one extra day the money per month is 8 . The minimum money per month must be at least 20 and have to been deducted from the money per month from the above money per month . Thank you a lot for your time !