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.
Hey!
I'm struggling with Power BI to show a table with the Score of the current month and previous month.
My test dataset is the following:
Period | Value | Score |
31/08/2022 | A | NoFav |
31/08/2022 | B | Neutral |
31/08/2022 | C | Neutral |
31/08/2022 | D | Neutral |
30/09/2022 | A | Fav |
30/09/2022 | B | NoFav |
30/09/2022 | C | Fav |
30/09/2022 | D | NoFav |
31/10/2022 | A | Fav |
31/10/2022 | B | Fav |
31/10/2022 | C | Fav |
31/10/2022 | D | NoFav |
And what I want to achieve is:
Value | Period | Score | ScorePreviousMonth |
A | 31/08/2022 | NoFav | |
A | 30/09/2022 | Fav | Nofav |
A | 31/10/2022 | Fav | Fav |
B | 31/08/2022 | Neutral | |
B | 30/09/2022 | NoFav | Neutral |
B | 31/10/2022 | Fav | NoFav |
To calculate the column ScorePreviousMonth i'm trying with this DAX formula, but it doesn't work and shows blanks instead the values:
ScorePreviousMonth =
MAXX(
FILTER ( ALL('table'), 'table'[Period] = (DATEADD('table'[Period], -1, MONTH )) )
, 'table'[Score]
)
I've created a Calendar table and tried as well, but with the same result.
ScorePreviousMonth =
MAXX(
FILTER ( ALL('table'), 'table'[Period] = PREVIOUSMONTH('Calendar'[Date] ))
, 'table'[Score]
)
Any idea?
Thanks in advance,
Regards
Solved! Go to Solution.
Hi @N4gash Try it colum
colum =
VAR _max = PREVIOUSMONTH('table'[Period])
return
MAXX(
FILTER (
ALL('table'),
EOMONTH('table'[Period],0) = _max
&& 'Table'[Value] = EARLIER([Value])
)
, 'table'[Score]
)
Hi @N4gash Try it colum
colum =
VAR _max = PREVIOUSMONTH('table'[Period])
return
MAXX(
FILTER (
ALL('table'),
EOMONTH('table'[Period],0) = _max
&& 'Table'[Value] = EARLIER([Value])
)
, 'table'[Score]
)
Thanks @amitchandak !!
I've tried your formula (adding "_max" to the filter) but the result is still wrong, it seems that uses the max Score for each Period -> NoFav at 31/08/2022 and 31/10/2022:
New col =
var _max = eomonth('table'[Period],-1)
return
MAXX(
FILTER ( 'table', Eomonth('table'[Period],0) = _max)
, 'table'[Score]
)
If I try with MINX instead MAXX:
How can I applied the MAXX for each row?
@N4gash , if you need a column
New col
=
var _max = eomonth('table'[Period],-1)
return
MAXX(
FILTER ( 'table', Eomonth('table'[Period],0) = )
, 'table'[Score]
)
for mesure try TI with date table
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |