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 There,
I have data over many weeks with different ID's and periods in one table. I need to see for a particular period, how many ID's have changes from one specific date to another (I would use a filter to filter on the Month's I want to compare). The scenarios I need to count for are:
1. ID's that have been removed
2. ID's that have moved periods
Example below:
ID | Date | Period |
A | May | Q1 |
B | May | Q1 |
C | May | Q1 |
A | June | Q1 |
B | June | Q3 |
C | June | Q1 |
B | July | Q3 |
C | July | Q1 |
Results should be as per below when comparing May with July:
Count of ID's removed = 1 (A)
Count of ID's changed period = 1 (B moved from Q1 to Q3)
Any help greatly appreciated 🙂
Hi All, I have managed to solve problem 1 (ID's that have been removed) but am still struggling with ID's that have moved quarters. I've tried using above suggestions but still can't get it to work. Any more ideas? thanks
@Hayleysea , I am hoping you have a date. I think this can be done using time intelligence. Like what blank this month vs last month.
I have a blog on the same. Their customer ID is like you ID
@Hayleysea Do you have an Index or actual Date column in your source data? Is that source data posted below? If not, can you post sample data? Without an Index or Date the calculation becomes kind of ugly trying to figure out previous.
I do have a proper date column that can be used and a week rank column.
@Hayleysea OK, slight error in previous reply, PBIX attached below sig. GROUPBY, not SUMMARIZE!! You want Page 3, Table (3)
Measure 3a =
VAR __Current = MAX([Date])
VAR __PreviousDate = MIN([Date])
VAR __TableCurrent = FILTER('Table (3)',[Date]=__Current)
VAR __TablePrevious = FILTER('Table (3)',[Date]=__PreviousDate)
VAR __Table = SELECTCOLUMNS('Table (3)',"ID",[ID],"Period",[Period])
VAR __Table1 =
EXCEPT(
SELECTCOLUMNS(__TablePrevious,"ID",[ID]),
SELECTCOLUMNS(__TableCurrent,"ID",[ID])
)
VAR __Table2 = FILTER(__Table,NOT([ID] IN __Table1))
VAR __Table3 = DISTINCT(__Table2)
VAR __Table4 = GROUPBY(__Table3,[ID],"Count",COUNTX(CURRENTGROUP(),[Period]))
RETURN
COUNTROWS(FILTER(__Table4,[Count]>1))
@ me if this doesn't work for you. Both are in the file.
@Hayleysea Whew! OK, the pattern goes along these lines:
Measure =
VAR __Current = MAX([Date])
VAR __PreviousDate = MIN([Date])
VAR __TableCurrent = FILTER('Table',[Date]=__Current)
VAR __TablePrevious = FILTER('Table',[Date]=__Previous)
RETURN
COUNTROWS(
EXCEPT(
SELECTCOLUMNS(__TablePrevious,"ID",[ID]),
SELECTCOLUMNS(__TableCurrent,"ID",[ID])
)
)
Something like that. Actual source data would be great to be more specific. I hesitate attempting the change one without proper sample data posted as text, a lot of variables to consider.
Maybe:
Measure =
VAR __Current = MAX([Date])
VAR __PreviousDate = MIN([Date])
VAR __TableCurrent = FILTER('Table',[Date]=__Current)
VAR __TablePrevious = FILTER('Table',[Date]=__Previous)
VAR __Table = SELECTCOLUMNS('Table',"ID",[ID],"Period",[Period])
VAR __Table1 =
EXCEPT(
SELECTCOLUMNS(__TablePrevious,"ID",[ID]),
SELECTCOLUMNS(__TableCurrent,"ID",[ID])
)
VAR __Table2 = FILTER(__Table,NOT([ID] IN __Table1))
VAR __Table3 = DISTINCT(__Table2)
VAR __Table4 = SUMMARIZE(__Table3,[ID],"Count",COUNT([Period]))
RETURN
COUNTROWS(FILTER(__Table4,[Count]>1))
So hard to test without data, might be missing something. Syntax errors.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |