Error with PREVIOUSMONTH with calculated column

[yolanda_meng]

Has anyone noticed a difference today on PowerBI.com around the PREVIOUSMONTH function?

We have a DirectQuery report that is using this function, the month part was a calculated column from a date field, it was working fine on PowerBI.com for long time. Today we started to notice that the chart with this function was broken, after testing, we realized it was the incompatibility of the function with a calculated column - the error went away after we switched to a non-calculated column. 

The calculated month column: Month = DATE(YEAR(Query1[CreatedDate]),MONTH(Query1[CreatedDate]),1)

The measure with PREVIOUSMONTH: TEST = CALCULATE(COUNT(Query1[ClientId]),PREVIOUSMONTH(Query1[Month]))

Please provide any feedbacks or any thoughts, thanks a lot!

[henriquesilveir]

@yolanda_meng hello, make sure that all date columns are okay, sometimes one row could have null values and it are 'crashing' your measure.

If all are fine let me know! 

[yolanda_meng]

Hi @henriquesilveir,

Thank you for your reply! Yes, we have noticed before that in Power BI desktop, if the date column is blank, the measure will break. However, this time we don't see the error in Power BI desktop as the date column is good, but then only break on app.powerbi.com. Any thoughts? 

[henriquesilveir]

@yolanda_meng hello , man use this solution to your:

https://community.powerbi.com/t5/Desktop/direct-query-previous-month/td-p/205439

May is necessary to create a dim calendar to your table, it can be the problem!

let me know if you got a solution , don't forget to mark this post as solution if you got it, other person may can have the same situation in the future!

[yolanda_meng]

Hi @henriquesilveir, 

This is the Month column we are using:

Month = DATE(YEAR('Table1'[CreatedDate]),MONTH('Table1'[CreatedDate]),1)

It's actually very similar to the post you mentioned. With this in the PREVIOUSMONTH measure, it was working fine until yesterday but not right now. Really not sure what's happened.. 

[henriquesilveir]

@yolanda_meng it's strange, please send me your .PBIX file with a little sample of data, and i will see for you, okay?

[yolanda_meng]

@henriquesilveirThanks for your help! 

But we are using DirectQuery so you might not able to open the pbix file we are using. I was able to reproduce the same error with a basic query - just select a date column from GETDATE() and use DAX to populate the Month column and use it with the PREVIOUSMONTH  function will break the chart.

[henriquesilveir]

@yolanda_meng try to create a Dim Calendar... perhaps some of your date can crash your model... make sure that you using a Dim Calendar to do this... it's recommend for PBI temporal measures. let me know if you got solution with a Dim Calendar.

[yolanda_meng]

@henriquesilveir, in my latest test, I was using this query as my data source and same error..

SELECT GETDATE() AS DateTest
UNION
SELECT DATEADD(month,-1,GETDATE())

Month column: 

Month = DATE(YEAR(Query1[DateTest]),MONTH(Query1[DateTest]),1)

Measure:

Previous Month = CALCULATE(COUNT(Query1[DateTest]),PREVIOUSMONTH(Query1[Month]))

Everything is so generic but still seeing the error as soon as we publish it to Power BI Service. 

[henriquesilveir · Accepted solution]

@yolanda_meng i got bad news. Now the PREVIOUSMONTH  is not supported into Directquery.

take a look in this article, and you will see: "This DAX function is not supported for use in DirectQuery mode. For more information about limitations in DirectQuery models, see http://go.microsoft.com/fwlink/?LinkId=219172."

After your see this article i reccomend you to see the second link, to see all limitations with DAX. It has been changed probally in this last update.

don't forget to mark this post as solution, other people can have the same situation in the future.

[yolanda_meng]

@henriquesilveirAhhh, puzzle solved with sad news...but thanks so much for you help! I will mark this as a solution. 

Thanks a lot!

[yolanda_meng]

Follow up question @henriquesilveir, is there a known measure that could replace PREVIOUSMONTH and acheive what it can do? Most likely other users come across this issue will have the same questions 🙂

[henriquesilveir]

I could use this post: 

https://community.powerbi.com/t5/Desktop/direct-query-previous-month/td-p/205439

The issue is to replace the PreviousMonth into other DAX to do the same thing... i hope that if helpfully!