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 cannot set my date dimension as Date Table because I have date gaps in the table entries. 

Measure = LASTDATE(Divs[Date].[Date]) works as expected but
Measure = PREVIOUSMONTH(Divs[Date].[Date]) returns Blank

No filters, no slicer, nothing obvious to me.

I am actually trying to do the following, but cannot get PREVIOUSMONTH to work
Last Month = CALCULATE(SUM(Divs[Amount]),PREVIOUSMONTH(Divs[Date].[Date]))

Why??

Thanks in advance. 
  • 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.

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super 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 ( )

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    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.