topic Calculate in row in DAX Commands and Tips

Calculate in row

bjurek — Thu, 23 Mar 2023 15:11:17 GMT

Hello!

Anybody knows how calculate "How many times in a row there is no availability counted from the last date"

Re: Calculate in row

amitchandak — Fri, 24 Mar 2023 05:33:34 GMT

bjurek , if you need a new column

if([Date] = maxx(filter(Table, [Shop] = earlier([Shop])), [Date]) , Countx(filter(Table, [Shop] = earlier([Shop]) && [availability] =0 ), [Date]) , blank())

for measures

if(MAx([Date]) = maxx(filter(allselected(Table), [Shop] = max([Shop])), [Date]) , Countx(filter(allselected(Table), [Shop] = Max([Shop]) && [availability] =0 ), [Date]) , blank())

Re: Calculate in row

bjurek — Tue, 04 Apr 2023 11:08:31 GMT

amitchandak

Hi, thanks for help, however its not work. Instead show value 8 it show 13 = (8+5 from 2023-01-01 to 2023-01-10)

Can you check ?

Re: Calculate in row

FreemanZ — Sat, 08 Apr 2023 04:13:48 GMT

hi bjurek

try to add a calculated column like:

Column = VAR _currentdate = tbl[Date] VAR _table = FILTER(tbl, tbl[Shop]=EARLIER(tbl[Shop])) VAR _stopdate = MAXX( FILTER( _table, tbl[Date]<_currentdate &&tbl[value]=1 ), tbl[Date] ) VAR RESULT= IF( tbl[Value]=0&&tbl[Date]=MAXX(_table, tbl[date]), COUNTROWS( FILTER( _table, tbl[Date]<=_currentdate &&tbl[Date]>_stopdate ) ), BLANK() ) RETURN RESULT

it worked like: