Previous month value of text column

[N4gash]

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

[DimaMD · Accepted 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]
) 

[nateschultz]

@DimaMD , Thank you for your easy to read example. Much appreciated!

[N4gash]

Awesome @DimaMD  it works!! 

Thanks

[N4gash]

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?

[amitchandak]

@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