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

[alee5210]

Hi All, I'm stuck with a tricky question. I have some data and I want to do the count or the sum based on multiple conditions that are given. The difficult aspect is that there are multiple date fields that all need to be filtered on and I only want to count or sum using the latest date for each unique ID.

First I have this reporting table, this is only showing June but it goes back many months. In the dashboard there are 2 single select slicer that the users MUST select

1. reporting_date which let's users look back at how things were in previous months

2. data_period which let's them see the past 1 year, 3 months, 1 month etc

reporting_date	data_period	start_calendar_date	end_calendar_date
01-June-24	From beginning	01-Jan-70	01-June-24
01-June-24	Last 1 year	01-June-23	01-June-24
01-June-24	Last 3 months	01-Feb-24	01-June-24
01-June-24	Last 1 month	01-May-24	01-June-24

My data looks like the following. The data works as a slow changing dimension table. So a unique ID's transit start date will never change, but if the mass of the item is remeasured throughout the journey, the eff_from (effective from) and eff_to (effective to) can be updated in our records.

Unique_ID	Transit_start	Mass	eff_from	eff_to
aaa	01-Apr-24	4	01-Apr-24	13-Apr-24
bbb	04-Apr-24	15	04-Apr-24	16-Apr-24
ccc	28-Apr-24	12	28-Apr-24	10-May-24
ddd	01-May-24	3	01-May-24	13-May-24
aaa	01-Apr-24	9	14-Apr-24	31-Dec-99
bbb	04-Apr-24	2	17-Apr-24	31-Dec-99
ccc	28-Apr-24	7	11-May-24	31-Dec-99
ddd	01-May-24	4	14-May-24	31-Dec-99

The rules to calculate the count/sum are:

For each unique ID, only use the latest record/row that meets the following conditions

1. the eff_from <= reporting_date

2. the eff_to > reporting date

3. start_calendar_date <= test_start_date 

4. end_calendar_date >= test_start_date

I would like this to be put into bar charts/line charts so maybe a Calculation Group would be effective here but I have not seen many examples on how to apply it to such a measure. Any help would be greatly appreciated.

[OwenAuger · Accepted solution]

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

[alee5210]

Hi Owen, this worked really well! Exactly what I was after. Thanks so much!