topic PREVIOUSMONTH returning blanks in DAX Commands and Tips

PREVIOUSMONTH returning blanks

jfpalumbo123 — Wed, 23 Nov 2022 18:46:31 GMT

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.

Re: PREVIOUSMONTH returning blanks

FreemanZ — Thu, 24 Nov 2022 00:52:58 GMT

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

Re: PREVIOUSMONTH returning blanks

v-yadongf-msft — Thu, 24 Nov 2022 06:19:16 GMT

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.

Re: PREVIOUSMONTH returning blanks

jfpalumbo123 — Fri, 25 Nov 2022 01:12:35 GMT

Looks like that is what I needed. TYVM!