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

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.