Power BI Service seems to behaving differently with dates.
In the desktop file I have changed the setting to not detect data types.
When I referesh the file in desktop, everything works fine, all the dates and the corresponding numbers are showing up correctly.
However, its a different story in the service. When the refresh takes place from the service, it converts the date format, which originally is dd/mm/yy to mm/dd/yy and due to which 1st of September becomes 9th of January and so the measures go haywire.
I have raised a ticket with Microsoft regarding this issue and it has been 2 months already and they have not been able to solve the problem. Here is the ticket number if anyone from Microsoft is reading this 120071524005859.
Time Intelligence is a HUGE part of Power BI and Business Intelligence in general and a product like Microsoft Power BI cannot mess it up.
My only question is that if it is working in the Power BI Desktop, it should work the same way in Power BI Service.
More than an idea, it is a request to fix the bug in the product/service.
I hope Microsoft resolves this problem soon before anyone else loses clients, like I did.