Forum Discussion

v-power's avatar
v-power
Frequent Visitor
7 years ago
Solved

Creating a Prior Month Column in Power BI

Hello All,    I have a table like below. I would like to create another column for Prior Month which would display previous months value as a column.    KPI Name Month Value KPI 1 7/31/2...
Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago

    Hi,

    I'm sure there are a few solutions to this. 

    If we use DATEADD in a measure like

     

    Prev Month Sale = CALCULATE(SUM(Table1[Value]), DATEADD(Table1[Month],-1,MONTH))
     
    The measure will return some correct values but some blanks.

    DATEADD won't always work in this example because the dates it works out have to exist in the table.  Let's take one case from the table - '30 sept 2018', subtract 1 month from this and we get '30 Aug 2018' (and that date doesn't exist in the table)

     

    What we need is a measure like this :

     

    Prev Month Sale 2 = CALCULATE(SUM(Table1[Value]), PREVIOUSMONTH(Table1[Month]))
v-power's avatar
v-power
Frequent Visitor
7 years ago

Hello BobiRussell 

 

Thanks for the response. I have both of the values in same table. And "Value" is not a measure I calculated. It is part of the original table. 

 

 

  • BobiRussell's avatar
    BobiRussell
    Frequent Visitor
    7 years ago

    Oka V-Power,

     

    So this is what I've used

     

    Gross Revenue LM = CALCULATE(
    [Gross Revenue], DATEADD( facts_Transactional[Invoice Date], -1, MONTH ) )
     
    Gross Revenue LM is a measure that I added to my "Key Measures" group. My Gross Revenue is a field that I created a measure but I could have used List Revenue that is already in the tabel like yours.
    My invoice date is in a different table so i had to reference the table name but it everything is in one table and want to add a column then you wont need the references the various tables.
     
    Hope that work for you!
    • v-power's avatar
      v-power
      Frequent Visitor
      7 years ago

      Thank you. I tried to search for the "value" field on my table1 using the calculate but it only lists the calculated measures I had created. Existing fields in the table dont show up for some reason.

      • HotChilli's avatar
        HotChilli
        Community Champion
        7 years ago

        Hi,

        I'm sure there are a few solutions to this. 

        If we use DATEADD in a measure like

         

        Prev Month Sale = CALCULATE(SUM(Table1[Value]), DATEADD(Table1[Month],-1,MONTH))
         
        The measure will return some correct values but some blanks.

        DATEADD won't always work in this example because the dates it works out have to exist in the table.  Let's take one case from the table - '30 sept 2018', subtract 1 month from this and we get '30 Aug 2018' (and that date doesn't exist in the table)

         

        What we need is a measure like this :

         

        Prev Month Sale 2 = CALCULATE(SUM(Table1[Value]), PREVIOUSMONTH(Table1[Month]))