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

zs6rop — Thu, 25 Nov 2021 07:25:14 GMT

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

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

amitchandak — Thu, 25 Nov 2021 08:25:36 GMT

zs6rop , Try a measure like

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

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

zs6rop — Thu, 25 Nov 2021 09:39:35 GMT

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

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

zs6rop — Thu, 25 Nov 2021 11:01:38 GMT

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

topic Re: Count the number of records where a planned dated changed between two dates in DAX Commands and Tips

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

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

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

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