Count the number of records where a planned dated changed between two dates

[zs6rop]

Hi All

I am creating a report where I need to see how many items (records) have had the Planned End Date changed in a certain time period (based on filters).

Any advise on how to do this, and effectiveness of my measures.

I have these measures:

Planned End Date - Range Latest =

// Find Planned End for the Last Selected Snapshot
Var vDate =
   IF(
      ISFILTERED(SnapshotDates),
         CALCULATE(
             MAX(SnapshotDates[SnapshotDate]),
             FILTER(SnapshotDates, SnapshotDates[SnapshotDate])
        ),
       CALCULATE(
         MAX(SnapshotDates[SnapshotDate]),
         ALL(SnapshotDates)
      )
   )
VAR vRetval =
   CALCULATE(
      SELECTEDVALUE(T_Items[PlannedEndDate]),
      FILTER(T_Items,T_Items[SnapshotDate]=vDate)
   )
Return vRetval

Planned End Date - Range Original =
// Find Planned End for the First Selected Snapshot
Var vDate =
   IF(
      ISFILTERED(SnapshotDates),
         CALCULATE(
             MIN(SnapshotDates[SnapshotDate]),
             FILTER(SnapshotDates, SnapshotDates[SnapshotDate])
        ),
       CALCULATE(
         MAX(SnapshotDates[SnapshotDate]),
         ALL(SnapshotDates)
      )
   )
VAR vRetval =
   CALCULATE(
      SELECTEDVALUE(T_Items[PlannedEndDate]),
      FILTER(T_Items,T_Items[SnapshotDate]=vDate)
   )
Return vRetval

Planned End Date Shift Months =
// Determine The number of Months Shifted

Var vRetval = DATEDIFF([Planned End Date - Range Original],[Planned End Date - Latest],MONTH)
Return if(vRetval<>0,vRetval)

Planned End Dates Changed =
// Determine if the End Date Shifted
IF([Planned End Date Shift Months] <> 0, "Yes","No")

On a weekly basis, the table (T_Items), id getting data by means of a job running and effectivly creating a snapshot of the transactional table.  Below is an example of the table with sample data.

Let's say my snapshot Start Date is 8 Jan 21 and the End date is 22 Jan 21, then 

The output I am seaking is twofold:

1. A table view indicating if the Item (ID) had a Pland End Date Changed, e.g. below:

ID	Planned Dated Changed
1	Yes
2	No
3	Yes

2. Number of items that had a change (This is where I need some help😞 

Yes	2
No	1

T_Items

ID	PlannedEndDate	Snapshot
1		01 Jan 21
1	05 Feb 21	08 Jan 21
1	05 Feb 21	15 Jan 21
1	10 Feb 21	22 Jan 21
1	10 Feb 21	29 Jan 21
2		01 Jan 21
2	01 Mar 21	08 Jan 21
2	01 Mar 21	15 Jan 21
2	01 Mar 21	22 Jan 21
2	15 Mar 21	29 Jan 21
3	01 Jun 21	15 Jan 21
3	15 May 21	22 Jan 21
3	15 May 21	29 Jan 21

[zs6rop · Accepted solution]

@amitchandak , for my spesific need, I had to tweak your formula to:

COUNTX(
FILTER(
SUMMARIZE(
FILTER(T_ITEMS,
// Only Look at reords that do have a Planned End Date
NOT(ISBLANK(T_ITEMS[PlannedEndDate]))
),
//group by
T_ITEMS[ID],
//Custom Col
"UEDCnt", DISTINCTCOUNT(T_ITEMS[PlannedEndDate])
),
[UEDCnt] > 1
), T_ITEMS[ID]
)

I am now getting what I am looking for

[amitchandak]

@zs6rop , Try a measure like

countx(filter(Summarize(Table, Table[ID], "_dis", distinctcount(Table[PlannedEndDate])) ,[_1]>1),[ID])

[zs6rop]

Hi, thanks.  This is so clever, I did not think about doing a distinct count on the Planned End Date.