Use Date Measure in other measure

[Anonymous]

I am putting together a table and one of the columns (Date 1) is a measure that returns a date format. I have a data table that has "Issue ID, Part Unique ID, reported date, issue category". How can I add another measure that comapres the reported date against Date 1 and count the distinct Issue IDs?

I am using the following formula but its not returning any values

Count of Issue before Date 1 = CALCULATE(

COUNTA('All Issues'[Issue ID]),FILTER(ALL('All Issues'),'All Issues'[Reported On] < [Date 1]))

I also tried this but I get an error

Count of Issue before Date 1 = CALCULATE(

COUNTX('All Issues',FILTER(ALL('All Issues'),'All Issues'[Reported On] < [Date 1])))

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

Count of Issue before Date 1 =
CALCULATE (
    COUNT ( 'All Issues'[Issue ID] ),
    FILTER (
        ALLSELECTED ( 'All Issues' ),
        'All Issues'[Part Unique ID] = MAX ( 'All Issues'[Part Unique ID] )
            && 'All Issues'[Reported On] < [Date 1]
    )
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[parry2k]

@Anonymous don't know what to say. If you cannot help by providing sample data, I have no way to figure this out. Good luck!

[parry2k]

@Anonymous I would recommend to throw  a sample pbix file with expected output and share it thru one drive/google drive.

[Anonymous]

Thats going to be difficult because a lot of it is sensitive information. I have a long formula measure that will return a date (Date 1). I want to use that date in another measure and count how many rows/issues are reported before that "calculated" date (Date 1 measure)

[parry2k]

@Anonymous what is the error?

[Anonymous]

@parry2k If I use the second formula the error is "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"