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'm quite new to the Power BI tool and I'm not sure how to perform the below grouping (if it's even possible).
I have a very simple dataset which looks like that:
I would like to aggregate the date by user and reduce the amount of entries. I tried to group by [User] with picking the MIN date from [Start Date] and MAX from [End Date] but the issues is that I would like to group only the months which are in sequence (are next to each other in calendar). Based on the above example I would like to achieve the below:
I will really appreciate help to determine if thats even possible in Power Query Editor.
Thank you in advance!
Solved! Go to Solution.
Hi @Mpcz
Here is a sample file with the proposed solution https://we.tl/t-lKvepWSJSd
This includes creating two calculated columns
Rank =
VAR CurrentRant =
RANKX ( Data, Data[Start Date],, ASC, Dense )
VAR AllDates =
CALENDAR ( MIN ( Data[Start Date] ), MAX ( Data[End Date] ) )
VAR DatesWithRank =
ADDCOLUMNS (
AllDates,
"@Rank", RANKX ( AllDates, YEAR ( [Date] ) * 100 + MONTH ( [Date] ),, ASC, Dense )
)
VAR RealRank =
MAXX ( FILTER ( DatesWithRank, [Date] = Data[Start Date] ), [@Rank] )
RETURN
RealRank - CurrentRant
Index = RANKX ( Data, Data[Rank],, ASC, Dense )
Then the measures would simply be
StartDate = MIN ( Data[Start Date] )
EndDate = MAX ( Data[End Date] )
Hi @Mpcz
Here is a sample file with the proposed solution https://we.tl/t-lKvepWSJSd
This includes creating two calculated columns
Rank =
VAR CurrentRant =
RANKX ( Data, Data[Start Date],, ASC, Dense )
VAR AllDates =
CALENDAR ( MIN ( Data[Start Date] ), MAX ( Data[End Date] ) )
VAR DatesWithRank =
ADDCOLUMNS (
AllDates,
"@Rank", RANKX ( AllDates, YEAR ( [Date] ) * 100 + MONTH ( [Date] ),, ASC, Dense )
)
VAR RealRank =
MAXX ( FILTER ( DatesWithRank, [Date] = Data[Start Date] ), [@Rank] )
RETURN
RealRank - CurrentRant
Index = RANKX ( Data, Data[Rank],, ASC, Dense )
Then the measures would simply be
StartDate = MIN ( Data[Start Date] )
EndDate = MAX ( Data[End Date] )
That's a big thank you! It's not the exact solution as I needed to do the transformation in power query but what I did, I replicated the steps from you measure in power query and it works like charm!
Hi,
I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
It is for creating a new table.
New Table =
VAR _newtable =
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Start Date], Data[End Date], User[User] ),
"@sortordernumber",
MAXX (
FILTER ( 'Calendar', 'Calendar'[Month start CC] = Data[Start Date] ),
'Calendar'[Month-Year sort order CC]
),
"@previoussortordernumber",
MAXX (
FILTER (
'Calendar',
'Calendar'[Month start CC]
=
VAR _currentstartdate = Data[Start Date]
VAR _currentuser = User[User]
VAR _previousstartdate =
MAXX (
FILTER (
Data,
Data[Start Date] < _currentstartdate
&& Data[User] = _currentuser
),
Data[Start Date]
)
RETURN
_previousstartdate
),
'Calendar'[Month-Year sort order CC]
)
)
VAR _addconditioncolumn =
ADDCOLUMNS (
_newtable,
"@condition",
IF ( [@sortordernumber] - [@previoussortordernumber] = 1, 0, 1 )
)
VAR _cumulatecondition =
ADDCOLUMNS (
_addconditioncolumn,
"@cumulate",
SUMX (
FILTER (
_addconditioncolumn,
Data[Start Date] <= EARLIER ( Data[Start Date] )
&& User[User] = EARLIER ( User[User] )
),
[@condition]
)
)
RETURN
SUMMARIZE (
GROUPBY (
_cumulatecondition,
User[User],
[@cumulate],
"@minstartdate", MINX ( CURRENTGROUP (), Data[Start Date] ),
"@maxenddate", MAXX ( CURRENTGROUP (), Data[End Date] )
),
User[User],
[@minstartdate],
[@maxenddate]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Mpcz , refer if one of the 2 can help
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 |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
139 | |
104 | |
104 | |
80 | |
66 |