I have a report that I publish to the PowerBi service that has been working well until we crossed over into 2021.
What I have is data coming from Microsoft Dynamics Account Schedules.
What happens I assumed Sales YTD numbers that come in would reset back to zero and there would be nothing for me to have to handle. I was wrong and what is happening is the YTD totals from 2020 are being carried over until our accounting department closes the year. The issue that I am dealing with is the data source that I am pulling from will not have certain numbers updated until almost the end of the first quarter of the year.
My approach is to just look at December of 2020, and if I am viewing data in any month in 2021, subtract the last numbers in December from the 2021 total and that would leave me with the 2021 YTD versus an aggregate number. My second concerns is at some point Accounting will close 2020 and then the 2021 numbers will then line up.
I can lookup the specific the record I want from December 2020 based in Date, KPI_Code, and department from December 2020.
Looking for suggestions on handling this issue. I have attached my sample PowerBi file. If you look at the file and change the slicer dates from December 2020 to January 2021 you will see that the YTD values stay the same due to the data coming in from Microsoft Nav.
DailyDoc2021.pbix (434.3 KB)
Also, can anyone tell me why I keep getting blank on the measure below.