Forum Discussion
Count/Sum Latest Record Per ID Based On Multiple Date Conditions
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.
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 ResultYou 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
2 Replies
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 ResultYou 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
Hi Owen, this worked really well! Exactly what I was after. Thanks so much!
