Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 , 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
@zs6rop , Try a measure like
countx(filter(Summarize(Table, Table[ID], "_dis", distinctcount(Table[PlannedEndDate])) ,[_1]>1),[ID])
Hi, thanks. This is so clever, I did not think about doing a distinct count on the Planned End Date.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |