CountIf in dax with fixed row

[Anonymous]

Dear Community,

how can I transfer this formula in excel to power bi Dax:

(30-COUNTIF(AF$61:AF61,"<>-"))

The first value is fixed in the column AF, and the range changes by 1 as it goes, so the next row is calculated like this:  COUNTIF(AF$61:AF62,"<>-"))  and so on as it goes to the next rows.

Thank you very much.

[Icey · Accepted solution]

Hi @Anonymous ,

Please check this:

Measure =
VAR CurDate_ =
    MAX ( 'YourTable'[Date] )
RETURN
    30
        - CALCULATE (
            COUNT ( 'YourTable'[ColumnName] ),
            'YourTable'[Date] <= CurDate_,
            'YourTable'[ColumnName] <> "-"
        )

Best Regards,

Icey

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

[AilleryO]

Hi,

So you're looking for a cumulative count with conditions ?

Do you have an index or a date in your data to do cumulative ?

Can you show us some dummy datas so we can help ?

Basically if you want a count per year you can use Time INtellignece function like TOTALYTD, or a CALCULATE with filters in it.

For example :

TOTALYTD([Sales Revenue],Sales[Date Invoice],//Cumulative sum until today from 1st january
ALL(TableDates))//For all dates

or for cumulative with index :

SUM running by lane (Measure) = 
VAR CurrentIndex=SELECTEDVALUE( 'TabData'[Index] )//Current row index
VAR CurrentLane = SELECTEDVALUE( 'TabData'[LaneID] )//Current Lane
RETURN
CALCULATE( 
SUMX( 'TabData' , 'TabData'[value] ),//Sum values
ALL( 'TabData'[Date operation] ),//for all dates
'TabData'[Index] <= CurrentIndex,//only for rows before
'TabData'[LaneID] = CurrentLane )//and for the lane computed

Hope those patterns will help

[Anonymous]

Hi AilleryO,

Thank you so much for your reply! I do have date column in my data. 
Can youplease explain this part 

'TabData'[Index] <= CurrentIndex,//only for rows before
'TabData'[LaneID] = CurrentLane )//and for the lane computed

a bit more in detail?

Thank you!

[AilleryO]

In my case I have an index column on my data with unique number increasing.

So to get a cumulative result, I'm doing the sum of all previous lines (index wise).

In other words, filtering all line before the one being computed (CurrentRow).

The other one with the lane is that in my case I was making a cumulative sum related to diffrent lanes, so I have to filter as well on the same lane as the one being computed (CurrentLane).

So if you have a date and no other filter, you just need the Index part of my example but adapted to your date.

Create a variable

CurrentDate = MAX( YourDate) or SELECTEDVALUE( YourDate )

And then use it  :

'TabData'[Date] <= CurrentDate

Hope it makes things clearer