Count/Sum Latest Record Per ID Based On Multiple Date Conditions

Hi alee5210 

I would suggest using INDEX to filter on the latest record per Unique ID. I take it that "latest" is based on the eff_from column.

(Sample PBIX attached)

Here is an example measure Mass Sum Latest, including the conditions you listed, assuming you already have Mass Sum = SUM ( Data[Mass] ):

Mass Sum Latest = 
VAR ReportingDate =
    SELECTEDVALUE ( Reporting[reporting_date] )
VAR StartCalendarDate =
    SELECTEDVALUE ( Reporting[start_calendar_date] )
VAR EndCalendarDate =
    SELECTEDVALUE ( Reporting[end_calendar_date] )
VAR SourceTable =
    CALCULATETABLE (
        SUMMARIZE ( Data, Data[Unique_ID], Data[eff_from] ),
        -- Use KEEPFILTERS to retain any existing filters on these columns
        KEEPFILTERS ( Data[eff_from] <= ReportingDate ),
        KEEPFILTERS ( Data[eff_to] > ReportingDate ),
        KEEPFILTERS ( StartCalendarDate <= Data[Transit_start] ),
        KEEPFILTERS ( EndCalendarDate >= Data[Transit_start] )
    )
VAR LatestRecords =
    INDEX (
        1,
        SourceTable,
        ORDERBY ( Data[eff_from], DESC ),
        DEFAULT,
        PARTITIONBY ( Data[Unique_ID] )
    )
VAR Result =
    CALCULATE ( [Mass Sum], LatestRecords )
RETURN
    Result

You can apply this logic to any other measure by replacing [Mass Sum] with any other measure, or SELECTEDMEASURE () in a calculation item. There is a calculation group with a single calculation item in the attached PBIX.

Is this the sort of thing you were looking for? Let me know if any of the logic is wrong.

Regards