Hello Enterprise DNA community, and a happy new Year from NYC!!
I’m struggling with a DAX concept/formula. Seems like it should be a simple pattern, but I’m spending days trying other advanced concepts that aren’t getting the results.
EGC Certification Example.pdf (342.2 KB)
Vertrees DataModel Skinnied.pbix (3.1 MB)
The Certification Data table below is show one property’s certification information for references. The Property and Certification date columns are key dimensions that are sliced by the user on the report.
Property ID Certification Date Certification Status Certification Type
Archer 1/1/1980 In Progress EGC
Archer 12/1/2020 Complete EGC
Archer 1/1/1980 In Progress LEED GOLD
The measure I need to create shows whether a certain type of certification was in progress or not for a particular period. “EGC Certifications In-Progress” should return the count of the records that have Certification Type = “EGC” has a status of “In Progress” for the last certification date <= the end date of the selected date filter (LastSelectedDate).
- For Q3, 2020 the measure should return 1, based upon the upon the 1/1/1980 record.
- For Q4, 2020 (and any future dates) this formula would return 0, as the EGC certification was completed on 12/1/2020.
Where I’m struggling is filtering the certification data table to show only the last certification record that is less that the LastDateSelected. The following formula below always shows a value of 1 for both Q3 2020 and Q4 2020. I’ve tried some advanced concepts using summarize and calculatetable (which have helped get some clarity on some dax possibilities) - but think that the community probably has the insights I need to add the additional filter restriction to my existing calculate statement
“EGC Certifications In Progress” = Var LastDateSelected = CALCULATE(MAX('RCH Calendar'[Date])) RETURN CALCULATE( SUMX( -- _FilteredCerts, 'Certification Data', IF('Certification Data'[Certification Date]=LastDate('Certification Data'[Certification Date]), IF('Certification Data'[Certification Status]="In Progress" && 'Certification Data'[Certification Type]="EGC", 1) ) ) , FILTER(ALL('RCH Calendar'), 'RCH Calendar'[Date]<=LastDateSelected))