Poor Performance of Report

[alexa_0028]

Hi All,

I have a big sales tables which contains invoice date.
I am calculating last_invoice as measure=   datediff(today(),max([Invoice Date]),DAY)

When I am adding this to my table it takes 30 mins to load. The number of records in my sales table is 30M.
Please help me 

[v-xiaoyan-msft]

Hi@alexa_0028， 

Complex Dax calculations do cause poor performance in reports, but the measure you are using is not very complex.

According to your description, there are more problems with memory usage and file size optimization, for better performance optimization, we recommend that you disable "Auto date/time" in data load options settings.

You can also clear the cache currently used and change the Maximum allowed(MB).

Article for reference: Improve Power BI Performance with Auto Date/Time and DAX Variables

If this doesn't work, consider whether it's a Date Dimension problem, and here's an article to help you understand it. Performance Design Tip for Power BI: Be Careful of Dates

I'm not sure if this is the only measure in your report, if there are others present, I suggest you improve your Power BI performance by optimizing your DAX, see this article for details. Improve Power BI Performance by Optimizing DAX

If this does not solve your problem, please provide more details and be free to let me know.

Hope it helps.

Best Regards,

Caitlyn Yan

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

[alexa_0028]

Hi @Fowmy ,
I tried above approach but I didn't get result.
It failed saying its using a lot of memory and the measure needs to simplify.

[Fowmy · Accepted solution]

@alexa_0028 

Try one of the following:

last_invoice  =   INT ( TODAY() -  SELECTEDVALUE ( TABLE[Invoice Date]) )
     

last_invoice  =   INT ( TODAY() - MAX ( TABLE[Invoice Date]) )

[alexa_0028]

This worked after creating it as calculated column instead of measure