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 Everyone,
This is probably very simple but has been breaking my spirit trying to resolve.
I have a dataset that looks like this:
And in the end highlighted column, I have calculated the using the following DAX:
Home Team Over 2.5 Count =
CALCULATE (
SUM ( Fixtures[Over 2.5] ),
FILTER (
Fixtures,
ISNUMBER ( Fixtures[Total Goals] )
&& Fixtures[Date] < EARLIER ( Fixtures[Date])
&& Fixtures[Home Team] = EARLIER(Fixtures[Home Team]))
)
As you can see, this calculation is totalling all the rows in the table where the date is before the current row date and the home team is equal to the current row home team. I would like to take this calculation further by having it sum only the 5 most recent rows where the home team is equal to the row home team and the dates are before the current row date.
I've tried to use TOPN function in what makes logical sense to me, but without success. Does anyone have any idea on how this could be done?
TIA
Solved! Go to Solution.
@Bvalen , Create rank column and trying using
new columns
rank = rankx(filter(Fixtures,Fixtures[Home Team] ,= EARLIER(Fixtures[Home Team]), Fixtures[Date],,asc,dense)
Home Team Over 2.5 Count =
CALCULATE (
SUM ( Fixtures[Over 2.5] ),
FILTER (
Fixtures,
ISNUMBER ( Fixtures[Total Goals] )
&& Fixtures[rank] < EARLIER ( Fixtures[rank]) && && Fixtures[rank] >= EARLIER ( Fixtures[rank]) -5
&& Fixtures[Home Team] = EARLIER(Fixtures[Home Team]))
)
Hi @Bvalen
The easiest way would be to add an index in PQ that gives you the order per date and home team. With that, you can just do the filtering based on
Fixtures[Index] >= (EARLIER ( Fixtures[Index]) - 5) && Fixtures[Index] < EARLIER ( Fixtures[Index])
It can also be done without the index, looking for the last 5 dates before the filtering, but it gets unnecessarily complicated.
Add some sample data and we can get more specific.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Bvalen , Create rank column and trying using
new columns
rank = rankx(filter(Fixtures,Fixtures[Home Team] ,= EARLIER(Fixtures[Home Team]), Fixtures[Date],,asc,dense)
Home Team Over 2.5 Count =
CALCULATE (
SUM ( Fixtures[Over 2.5] ),
FILTER (
Fixtures,
ISNUMBER ( Fixtures[Total Goals] )
&& Fixtures[rank] < EARLIER ( Fixtures[rank]) && && Fixtures[rank] >= EARLIER ( Fixtures[rank]) -5
&& Fixtures[Home Team] = EARLIER(Fixtures[Home Team]))
)
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |