Get value from one month ago

[GraceN]

Hi! I am new to Power BI, and I want to create a measure that returns a value corresponding to the previous month. I don't want to use a slicer. I think I want to use the CALCULATE function, but I don't know how to set it up.

Here is an Excel table with dummy data. It is now December and I want the values from November. In this example, I want measures to return Previous_Month_Actual = 52 and Previous_Month_Target = 55 

Thanks for your help

Month	Actual	Target
01/31/17	5	5
02/28/17	12	10
03/31/17	18	15
04/30/17	21	20
05/31/17	25	25
06/30/17	27	30
07/31/17	30	35
08/31/17	39	40
09/30/17	44	45
10/31/17	53	50
11/30/17	52	55
12/31/17		60
01/31/18		65
02/28/18		70
03/31/18		75
04/30/18		80
05/31/18		85
06/30/18		90

[GraceN]

 Hi Wolf:

Thanks for the suggestion but inexplicably it's a partial solution. I was able to get the correct calculation for this specific example (although I need to change the semicolons in your formula to commas). On the other hand, I don't get the correct values with any other data set even when I used the same Excel table format for the source. That is,  I get unexpected values for  ActualLastMonth and TargetLastMonth, which are inconsistent, even though the values for ActualMeasure and TargetMeasure are correct. Here are 2 screenshots 1) the first dummy data set which worked, and 2) another dummy data set with the wrong values in the cards. I'm including the new measure in both

Dataset 1: this workedDataset 2: this didn't work

[WolfBiber]

Hi,

try following formula:

YourMeasure = CALCULATE(SUM(TheTable[Actual]); PREVIOUSMONTH(TheTable[Month]))

Greetings,

Wolf

[GraceN]

Thanks, that partially worked. I created this measure, and I can see the last month's values in a new column in my table. 

On the other hand, I only want a scalar result that reflects the Actual or Target value for the previous month (rather than a new column of values). That is, I want the measure to show Actual_last_month = 52 and Target_last_month = 55

[Phil_Seamark]

Did you try the suggestion by @WolfBiber?  That uses a calculated measure so may be better suited to this

[GraceN]

Yes, I tried that. Please see my response in the previous message. 

[GraceN]

To clarify, I want to display a single value from last month without the need for a slicer or selecting a row. When I use the card visualization, the value shows up as Blank. Only when I highlight a row in my table will the card show a value. I want my card to display last month's value of 52 for YourMeasure without having to click on 12/31/17 in the table.

  

In October, wieke ased the same question and there was no complete solution (even though it says Solved)

https://community.powerbi.com/t5/Desktop/Previous-month-value/m-p/266898/highlight/true#M120382

[Phil_Seamark]

So just to clarify, you want the KPI that is showing as (blank) to show the latest value when nothing is selected, otherwise when a selection has been made, it's currently working perfectly?

[Phil_Seamark]

This might be getting close (with a few tweaks to suit)

YourMeasure = 
VAR LastDateWithalue = CALCULATE(LASTDATE('Table1'[Month]),'Table1'[Actual]<>BLANK())
VAR LatestValue =  CALCULATE(SUM('Table1'[Actual]),'Table1'[Month] = LastDateWithalue)
RETURN
IF(HASONEVALUE('Table1'[Month]),
				CALCULATE(
					SUM(Table1[Actual]), 
					PREVIOUSMONTH(Table1[Month])
					)
 	, LatestValue)

[GraceN]

Hi Phil:

Thanks for your answer, but similar to srivint's measure, it only works for the Actual Last Month measure and not the Target Last Month measure. The Target column contains values to June 2018, and your measure returns a value of 90 rather than 55 

[srinivt]

This should do the trick but you could tweak your DAX a bit to make it more concise and readable.

IF(

       HASONEVALUE('Table1'[Month]),

       CALCULATE( SUM(Table1[Actual]), PREVIOUSMONTH(Table1[Month]) ),

       CALCULATE( SUM(Table1[Actual]), CALCULATETABLE(LASTDATE(Table1[Month]), NOT(ISBLANK(Table1[Actual])))) 

)

[GraceN]

Hi. Thanks for your solution, but it only works for the Actual Last Month measure because that field contains values only through Nov 2017, i.e. the previous month. The Target column shows values to June 2018 so your measure gives a value of 90 (rather than 55 which corresponds to Nov 2017).

[WolfBiber]

Hey, you can use Filter with calculate an Today() function. Try the following expression:

LastMonth = CALCULATE([TheMeasure];Table1;month(Table1[Month])=MONTH(TODAY()))

Where TheMeasure is the same I posted:

TheMeasure = CALCULATE(SUM(Table1[Actual]); PREVIOUSMONTH(Table1[Month]))

Greetings,

Wolf

[Phil_Seamark]

Hi @GraceN

If you are happy to add as a calculated column to your table.  You could try this.  Just repeate for target, but change the column used in the SUM function.  This assumes your previous value always falls on the last day of each month.

Previous Acutal = 
VAR D = 'Table1'[Month]
RETURN CALCULATE(SUM(Table1[Actual]),
            FILTER(ALL('Table1'),
                'Table1'[Month] =  DATE(YEAR(D),MONTH(d),1)-1
                )
                )

[GraceN]

Thanks but unfortunately I got this error message when I tried that

• A single value for column 'Month' in table 'Sheet1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.