Forum Discussion

jfpalumbo123's avatar
jfpalumbo123
Frequent Visitor
3 years ago
Solved

PREVIOUSMONTH returning blanks

Just trying to use PREVIOUSMONTH function but I always get blanks. Looking through other posts...I am using Auto Time Intelligence and my column is set to type of Date and I see the Date Hierarchy. I...
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi jfpalumbo123 ,

     

    I think you'd better create a calendar table.

     

    This is my test table:

     

    If I don't create a calendar table, I will get blank result:

    CurrentMonth_sales = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Month] = SELECTEDVALUE('Table'[Month])))

PreviousMonth_sales_1 = CALCULATE(SUM('Table'[Sales]),PREVIOUSMONTH('Table'[Date]))

     

    If I create a calendar table:

    CalendarTable = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Month",MONTH([Date]))

     

    and create relationship between two tables:

     

    You will get correct result:

    PreviousMonth_sales_2 = CALCULATE(SUM('Table'[Sales]),PREVIOUSMONTH('CalendarTable'[Date]))

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

try not to use Auto Time Intelligence and try to build your own Date table. The easiest way is to create a new table with the code below:

Date = CALENDARAUTO ( )