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

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's avatar
    GraceN
    Frequent Visitor
    8 years ago

    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).