How to create measure to display value from previous label without calculated column

[Anonymous]

Hi,

How can I create a measure which can be populated for each row in [# Issues in Prev Month] based on [# Issues] of the previous label without using a calculated column? The label is actually a month, but is not in the default month format.

	# Issues	# Issues in Prev Month
2018M12	5	0
2019M01	3	5
2019M02	4	3
2019M03	5	4

[Mariusz · Accepted solution]

Hi @Anonymous 

Please see the example below

LM Sales =

CALCULATE(

[Sales Amount], -- Mesure 

PREVIOUSMONTH('Date'[Date]) --PREVIOUSMONTH function with Date field from Calendar table

)

Hope this helps
Mariusz

[Anonymous]

Hi @Mariusz ,

I have tried the PREVIOUSMONTH function but to not much success... The label does not seem to be recognised as a month.

The error says "A column specified in the call to function 'PREVIOUSMONTH' is not of type 'DATE'. This is not supported."

[Mariusz]

Hi @Anonymous 

You can use the below patern 

Add Column // 

Rank =

RANKX(

ALL(Table1),

INT(SUBSTITUTE(Table1[Month], "M", "")),,

ASC

)

Add Measure:~

PM Value =

VAR PM = MIN(Table1[Rank]) -1

RETURN

CALCULATE(

SUM(Table1[Value]),

ALL(Table1),

Table1[Rank] = PM

)

[Anonymous]

Hi @Mariusz, Unfortunately this also does not work as my column of values are actually calculated values and not really a column in the data model, so it seems like the sum() function will not accept my "column" of measures. Thanks for your help. I will try to obtain the column of dates to make the PREVIOUSMONTH work instead.

[Mariusz]

Hi @Anonymous 

You can use MAX instead 

Mariusz