Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
This is my current data table structure:
Date Name
---------------------
01.03.2018 Mark
02.03.2018 Mark
03.03.2018 Mark
07.03.2018 John
08.03.2018 John
15.03.2018 Steve
What I would like to achieve is kind of grouping by consecutive dates, so that I have at the end something like this:
Date from Date Until Name
------------------------------------
01.03.2018 03.03.2018 Mark
07.03.2018 08.03.2018 John
15.03.2018 15.03.2018 Steve
Any tips? Thank you in advance!
Solved! Go to Solution.
Essentially I have added 3 calculated columns to identify the boundaries of consective dates which can then be used for Groupings
SeriesBoundaries = VAR PriorName = CALCULATE ( VALUES ( TableName[Name] ), FILTER ( ALLEXCEPT ( TableName, TableName[Name] ), TableName[SeriesStart] = EARLIER ( TableName[SeriesStart] ) - 1 ) ) VAR NextName = CALCULATE ( VALUES ( TableName[Name] ), FILTER ( ALLEXCEPT ( TableName, TableName[Name] ), TableName[SeriesStart] = EARLIER ( TableName[SeriesStart] ) + 1 ) ) RETURN IF ( PriorName <> TableName[Name], "Series Start", IF ( NextName <> TableName[Name], "Series End" ) )
HI @lkarolak
Please change the formua of Series Start as follows
SeriesStart = VAR PreviousDate = CALCULATE ( MAX ( TableName[Date ] ), FILTER ( TableName, TableName[Date ] < EARLIER ( TableName[Date ] ) ) ) VAR PreviousName = CALCULATE ( FIRSTNONBLANK ( TableName[Name], 1 ), FILTER ( TableName, TableName[Date ] = PreviousDate ) ) VAR myrank = RANKX ( TableName, TableName[Date ],, ASC, DENSE ) RETURN IF ( PreviousDate <> TableName[Date ] - 1 && TableName[Name] = PreviousName, myrank + 1, myrank )
Hi,
Would anyone be able to help me with an extension of this solution? I would like to group by consecutive date ranges as previous example but I would like the dates to be grouped even though there is a weekend/holiday within the date range.
So if the dates are:
Friday, 01 March 2018
Monday, 04 March 2018
Tuesday, 05 March 2018
They should still be grouped as consecutive.
Many thanks in advance
HI @lkarolak
You can use a Table Visual..
Place the Name Field in values. Drag the Date Field in the Values Section twice and choose the earliest and latest aggreagtions
Or you can create a calculated Table
from the Modelling Tab>>New Table
Table = SUMMARIZE ( TableName, TableName[Name], "Date From", MIN ( TableName[Date] ), "Date Until", MAX ( TableName[Date] ) )
Thank you @Zubair_Muhammad
This approach with SUMMARIZE is fine, but when there are more than one ranges for a name, then it is not working correctly. I mean, if I have:
Date Name
---------------------
01.03.2018 Mark
02.03.2018 Mark
03.03.2018 Mark
07.03.2018 John
08.03.2018 John
15.03.2018 Steve
20.04.2018 Mark
21.04.2018 Mark
22.04.2018 Mark
Then the result for "Mark" would be :
Date from Date Until Name
------------------------------------
01.03.2018 22.04.2018 Mark
Which is wrong for my scenario.
I would need something like this:
Date from Date Until Name
------------------------------------
01.03.2018 03.03.2018 Mark
07.03.2018 08.03.2018 John
15.03.2018 15.03.2018 Steve
20.04.2018 22.04.2018 Mark
Thank you!
Essentially I have added 3 calculated columns to identify the boundaries of consective dates which can then be used for Groupings
SeriesBoundaries = VAR PriorName = CALCULATE ( VALUES ( TableName[Name] ), FILTER ( ALLEXCEPT ( TableName, TableName[Name] ), TableName[SeriesStart] = EARLIER ( TableName[SeriesStart] ) - 1 ) ) VAR NextName = CALCULATE ( VALUES ( TableName[Name] ), FILTER ( ALLEXCEPT ( TableName, TableName[Name] ), TableName[SeriesStart] = EARLIER ( TableName[SeriesStart] ) + 1 ) ) RETURN IF ( PriorName <> TableName[Name], "Series Start", IF ( NextName <> TableName[Name], "Series End" ) )
Thank you @Zubair_Muhammad, it almost works 🙂
This is the actual initial data that I have:
But then I get this after using the approach you suggested. The first row is exactly what I want, however, the "single" ones are still listed:
What am I doing wrong?
Thanks!
@Zubair_Muhammad My mistake, forgot to set the filter on visual level to match the "SeriesStart".
It's working good now, thanks a lot for your support!
@Zubair_Muhammad Sorry, actually there is something missing still.
If we have this example:
Date Name
----------------------
01.04.2018 Claudia
03.04.2018 Claudia
Then this is treated as one range (01.04.2018-03.04.2018), although it should be two separate 1 day ranges.
See the example file here: click
Thank you!
HI @lkarolak
Please change the formua of Series Start as follows
SeriesStart = VAR PreviousDate = CALCULATE ( MAX ( TableName[Date ] ), FILTER ( TableName, TableName[Date ] < EARLIER ( TableName[Date ] ) ) ) VAR PreviousName = CALCULATE ( FIRSTNONBLANK ( TableName[Name], 1 ), FILTER ( TableName, TableName[Date ] = PreviousDate ) ) VAR myrank = RANKX ( TableName, TableName[Date ],, ASC, DENSE ) RETURN IF ( PreviousDate <> TableName[Date ] - 1 && TableName[Name] = PreviousName, myrank + 1, myrank )
Hi @Zubair_Muhammad ,
I am not able to get that lastDate part. Could you please share the file?
thanks,
Srikanth
Hi @Zubair_Muhammad,
I have a very similar problem with one critical difference, my source data can have multiple transaction on the same date. See below:
In my requirement, I want to ignore the second transaction for Mark on the 1st March 2018. This is the result I need:
Date | Name | SeriesStart | SeriesBoundaries | Last Date |
Thursday, 01 March 2018 | Mark | 1 | Series Start | Thursday, 01 March 2018 |
Thursday, 01 March 2018 | Mark | 1 | ||
Saturday, 03 March 2018 | Mark | 3 | Series Start | Saturday, 03 March 2018 |
Wednesday, 07 March 2018 | John | 3 | Series Start | Thursday, 08 March 2018 |
Thursday, 08 March 2018 | John | 4 | Series End | |
Thursday, 15 March 2018 | Steve | 5 | Series Start | Thursday, 15 March 2018 |
Tuesday, 20 March 2018 | Mark | 6 | Series Start | Thursday, 22 March 2018 |
Wednesday, 21 March 2018 | Mark | 7 | ||
Thursday, 22 March 2018 | Mark | 8 | Series End | |
Sunday, 01 April 2018 | Claudia | 9 | Series Start | Sunday, 01 April 2018 |
Tuesday, 03 April 2018 | Claudia | 11 | Series Start | Tuesday, 03 April 2018 |
How would you change your code to handle this situation? I have re-uploaded your solution with my requirement here:
Thanks
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
108 | |
96 | |
95 | |
74 | |
71 |