Forum Discussion

RichBurdick's avatar
RichBurdick
Helper I
5 years ago
Solved

Help With Measure for Date

I have data  from two tables that I am trying to use to get one date.  One table is an audt trail table that holds the date(s) a record was closed (or re-closed).  I have filtered only the activities I want to use in Power Query.

 

The audit trail table has the following:

  • ID field
  • Activity Name field
  • Date Performed field
  • Source State field
  • Destination State field

The other table is my CAPA table and has the following:

  • ID field
  • Short Description field
  • Overall Target Due Date field
  • Overall Completion Date field
  • Status

Here is what I am trying to do via DAX,  I want to create a clousre date where we use the earliest date from the Date Performed field from the audit trail table when that date is > Overall Completion Date field.

 

Any ideas how to do this?

 

  • rfigtree's avatar
    rfigtree
    5 years ago

    assuming tables linked on id field and the capa table is a Dimention.

     

    mClosure:=
CALCULATE(MIN(tblFact[dateperformed]),
          FILTER(tblFact,tblFact[dateperformed]>RELATED(tblDIM[overallcompletiondate]))
          )

     

     

1 Reply

  • rfigtree's avatar
    rfigtree
    Resolver III
    5 years ago

    assuming tables linked on id field and the capa table is a Dimention.

     

    mClosure:=
CALCULATE(MIN(tblFact[dateperformed]),
          FILTER(tblFact,tblFact[dateperformed]>RELATED(tblDIM[overallcompletiondate]))
          )