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

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

    • srinivt's avatar
      srinivt
      Microsoft 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).

         

    • GraceN's avatar
      GraceN
      Frequent Visitor
      8 years ago

      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