Count short/long terms???

[Anonymous]

Hello, 

I need a DAX Formula that counts only rows from 15 Columns, if there are more than 30 rows with (Text) values with no interruption (long term). Thanks in advance !

[v-jingzhang]

Hi @Anonymous 

I guess every column represents a car and every row represents a day? Do you want to count days of every rent term for every car?

Best Regards,
Community Support Team _ Jing

[Anonymous]

I want to count only long rent terms (>30 non-empty rows)

[v-jingzhang]

Hi @Anonymous 

The table structure is not friendly for DAX, so I transform it into below structure in Power Query editor. Download the attachment for details. 

 

Then create two calculated columns with the following DAX expressions

Flag = 
var _value = 'Table'[Value]
var _previousValue = MAXX(FILTER('Table','Table'[Car ID]=EARLIER('Table'[Car ID])&&'Table'[Index]=EARLIER('Table'[Index])-1),'Table'[Value])
return
IF(_value=""||ISBLANK(_value),0,IF(_value=_previousValue,2,1))  // 0->Not rented day, 1->First rented day, 2->Continuous rented day

Term Day = 
var _firstIndex = IF('Table'[Flag]=1,'Table'[Index],IF('Table'[Flag]=2,MAXX(FILTER('Table','Table'[Car ID]=EARLIER('Table'[Car ID])&&'Table'[Flag]=1&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[Index])))
var _lastIndex = IF('Table'[Flag]<>0,'Table'[Index])
return
IF('Table'[Flag]<>0, _lastIndex - _firstIndex + 1)

Finally create a measure

Number of Long Terms = COUNTROWS(FILTER('Table','Table'[Term Day]=31))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Samarth_18]

Hi @Anonymous ,

Could you please explain your question with some example or sample data with expected output?

It would help you get solution for your question more quickly

Thanks,

Samarth

[Anonymous]

I have a Table with car models. Some cars belong to the company and some are rented. I need to count the number of days for the rented cars, but I have to differentiate long term rented days from short term rented days. Long term= more than 30 days with no interruption, short term= less than 30 days

[Anonymous]

[Anonymous]

@amitchandak maybe you know ?