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

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

GraceN's avatar
GraceN
Frequent Visitor
8 years ago

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

    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'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