Forum Discussion

felipesaw's avatar
felipesaw
Frequent Visitor
4 years ago
Solved

Dax or power query

Hello I need help to make this column "HELP"

 

If the value from "Value" to positive, just repeat that value in HELP.

If the value from "Value" to negative:

      -If the previous value value is positive, then I put the negative value in HELP.

      -If the last two values of negative values, i add both and put in HELP.

             - If the next value for positive discount this negative value or sum of negative values of HELP

 

 

DataValueHELP
01/jan/21100100
01/fev/21200200
01/mar/21-300-300
01/abr/21500200
01/mai/21100100
01/jun/21-200-200
01/jul/21-300-500
01/ago/211000500
01/set/21100100
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi felipesaw 

    Thanks for reaching out to us.

    Just create 2 measures,

    count = 
var _pre=MAXX(FILTER(ALL('Table'),'Table'[Data]<MIN('Table'[Data]) && 'Table'[Value]>=0),[Data])
return CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Data]>_pre && 'Table'[Data]<MIN('Table'[Data])))
    Test = 
var _start=IF([count]<>BLANK(), MAXX(FILTER(ALL('Table'),'Table'[Data]<MIN('Table'[Data])&&ISBLANK([count])),[Data]))
var _sum=SUMX(FILTER(ALL('Table'),'Table'[Data]<=MIN('Table'[Data]) &&'Table'[Data]>=_start ),[Value])
return IF(ISBLANK([count]),MIN('Table'[Value]),_sum)

    result

     

    Best Regards,

    Community Support Team _Tang

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

1 Reply

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi felipesaw 

    Thanks for reaching out to us.

    Just create 2 measures,

    count = 
var _pre=MAXX(FILTER(ALL('Table'),'Table'[Data]<MIN('Table'[Data]) && 'Table'[Value]>=0),[Data])
return CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Data]>_pre && 'Table'[Data]<MIN('Table'[Data])))
    Test = 
var _start=IF([count]<>BLANK(), MAXX(FILTER(ALL('Table'),'Table'[Data]<MIN('Table'[Data])&&ISBLANK([count])),[Data]))
var _sum=SUMX(FILTER(ALL('Table'),'Table'[Data]<=MIN('Table'[Data]) &&'Table'[Data]>=_start ),[Value])
return IF(ISBLANK([count]),MIN('Table'[Value]),_sum)

    result

     

    Best Regards,

    Community Support Team _Tang

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