DAX Calculation - Filtering for latest record <= last sliced date

DAX DAX Calculations
1

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))
2

Hi @kevin.vertrees. If possible, please attach you work-in-progress PBIX file and an Excel mock-up of your desired outcome to help the forum members visualize/investigate your issue. If you include DAX code in your post, please format it with the </> button above
Greg

3

Hi @kevin.vertrees , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

4

Thanks Greg - i have created a simplied pbix file and a pdf that shows the desired result.

5

I was able to figure out the DAX to return was I was looking for. Found the pattern in the “Definitive Guide to DAX” under the “Using LastDate and LastNonBlank” section.

EGC Certification In Progress =
VAR LastDateSelected = CALCULATE ( MAX ( ‘RCH Calendar’[Date] ) )
RETURN
CALCULATE (
SUMX (
VALUES ( ‘Certification Data’[RCH_Unique_ID] ),
CALCULATE (
SUM ( ‘Certification Data’[In Progress] ),
LASTNONBLANK (
‘Certification Data’[Certification Date],
‘Certification Data’[Certification Date]
)
)
),
FILTER ( ALL ( ‘RCH Calendar’ ), ‘RCH Calendar’[Date] <= LastDateSelected ),
‘Certification Data’[Certification Type] = “EGC”
)