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

 

MonthActualTarget
01/31/1755
02/28/171210
03/31/171815
04/30/172120
05/31/172525
06/30/172730
07/31/173035
08/31/173940
09/30/174445
10/31/175350
11/30/175255
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

 

 

Need Help

14 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

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

      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.

       

       

  • WolfBiber's avatar
    WolfBiber
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi,

    try following formula:

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

     

    Greetings,

    Wolf

    • GraceN's avatar
      GraceN
      Frequent Visitor
      8 years ago

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

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

  • GraceN's avatar
    GraceN
    Frequent Visitor
    8 years ago

     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