Forum Discussion

GraceN's avatar
GraceN
Frequent Visitor
8 years ago

Get value from one month ago

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 do...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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)
WolfBiber's avatar
WolfBiber
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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