Count of Active records based from Start and End Date

[dantabangcura]

Hi,

I am currently building a report in which will count the number of records which has a "blank" termination date.

And also, what i want to do is whenever i select a specific year, it will count all the records from the beginning to the selected date.

Ex.

Selected year = 2016

Data to Display = All previous records up to selected year which is 2016

Thank you

[v-chuncz-msft · Accepted solution]

@dantabangcura,

You may refer to the following DAX that adds a measure.

Measure =
IF (
    HASONEVALUE ( 'Calendar'[Date].[Year] ),
    CALCULATE (
        COUNTBLANK ( Table1[End Date] ),
        YEAR ( Table1[Start Date] ) <= VALUES ( 'Calendar'[Date].[Year] )
    )
)

[vanessafvg]

@dantabangcura for blank values you can use

=COUNTBLANK(table[terminationdate])

create a measure for 

records for the year = calculate (countrows(table), year(date) =   2016)