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.
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
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 |