Measure works in Power BI Desktop but not after publishing

[soundguy2]

I created a DAX measure to show me the total of the most recent day's sales, regardless of filter context.  The measure seems to function correctly in Power BI Desktop, but any visuals using it produce an error message when published to Power BI Service.

The measure looks like this.  Is there any reason it would work in Desktop but not on Power BI Service?

Latest Sales =
    CALCULATE([Total Sales],
        FILTER(ALL('Calendar'),
            'Calendar'[Date] = MAX('Sales Data'[DateCreated])
        )
    )

[BDale93]

This issue was infuriating for me. I finally narrowed the issue down to a data type that I changed in my model not transferring over to the web service. So, instead of changing the data type in the model, I changed it within power query and that seemed to resolve the issue when publishing. Hope this helps

[lbendlin]

What does the error message say?

[soundguy2]

All it really says is that 'something's wrong' and points me to the affected measure.  This only happens after it's been published.

[lbendlin · Accepted solution]

Can you confirm that your Calendar table on the service dataset is contiguous and covering?  You can use DAX Studio with the service dataset as an XMLA endpoint to check that.

[soundguy2]

It's working!  You're correct that the issue was with the Date (calendar) table.  Thanks for the help.

Regards,

Brian

[soundguy2]

Hi Ibendlin,

I think you're on to something with the date table.  The date table is generated from a script, so it's contiguous and covers the range of the data.  However, it's stored as Datetime format and maybe this is conflicting with the server time when published to Power BI Service.  I'll try changing to Date format and I'll post tomorrow whether that fixes the issue.  Thanks for the direction.

Brian