cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
power_star_77 Frequent Visitor
Frequent Visitor

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

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
2018M1250
2019M0135
2019M0243
2019M0354
1 ACCEPTED SOLUTION

Accepted Solutions
Mariusz Established Member
Established Member

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

Hi @power_star_77 

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
5 REPLIES 5
Mariusz Established Member
Established Member

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

Hi @power_star_77 

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
power_star_77 Frequent Visitor
Frequent Visitor

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

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 Established Member
Established Member

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

Hi @power_star_77 

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
)

 

power_star_77 Frequent Visitor
Frequent Visitor

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

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 Established Member
Established Member

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

Hi @power_star_77 

 

You can use MAX instead 

Mariusz