Forum Discussion
Solved
Microsoft 365 Usage Analytics PBI report issue for 2021
Dear PBI Community, We are using the Microsoft 365 Usage Analytics PBI report. We find it very useful for our M365 adoption metrics. Since the turn of the year (2021), I cannot filter 2021 da...
Here's a workaround provided by Microsoft while they fix the template this will allow you to extend the calendar dates:
After opening the PBIT (or the PBIX) go to “TenantMailboxUsage” and copy the formulas related to the following calculated columns to Notepad:
StorageUsed(KB/MB/GB/TB) = IF([TotalStorageUsed]/1024 <= 1, FIXED([TotalStorageUsed],2,1)& " " & "Bytes",IF([TotalStorageUsed]/1048576 <= 1 && [TotalStorageUsed]/1024 > 1, FIXED([TotalStorageUsed]/1024,2,1) &" " & "KB",IF([TotalStorageUsed]/1073741824 <= 1 && [TotalStorageUsed]/1048576 > 1,FIXED([TotalStorageUsed]/1048576,2,1) & " " & "MB",IF([TotalStorageUsed]/1073741824 > 1 && [TotalStorageUsed]/1099511627776 <= 1 ,FIXED([TotalStorageUsed]/1073741824,2,1) & " " & "GB",IF([TotalStorageUsed]/1099511627776 >= 1,FIXED([TotalStorageUsed]/1099511627776,2,1) & " " & "TB","0")))))
TotalIssueWarningQuota(KB/MB/GB/TB) = IF([IssueWarningQuota_calc]/1024 <= 1, FIXED([IssueWarningQuota_calc],2,1)& " " & "Bytes",IF([IssueWarningQuota_calc]/1048576 <= 1 && [IssueWarningQuota_calc]/1024 > 1, FIXED([IssueWarningQuota_calc]/1024,2,1) &" " & "KB",IF([IssueWarningQuota_calc]/1073741824 <= 1 && [IssueWarningQuota_calc]/1048576 > 1,FIXED([IssueWarningQuota_calc]/1048576,2,1) & " " & "MB",IF([IssueWarningQuota_calc]/1073741824 > 1 && [IssueWarningQuota_calc]/1099511627776 <= 1 ,FIXED([IssueWarningQuota_calc]/1073741824,2,1) & " " & "GB",IF([IssueWarningQuota_calc]/1099511627776 >= 1,FIXED([IssueWarningQuota_calc]/1099511627776,2,1) & " " & "TB","0")))))
2- remove the formula and put something simple instead:
StorageUsed(KB/MB/GB/TB) = 1
TotalIssueWarningQuota(KB/MB/GB/TB) = 1
3- Go to Calendar table and change the formula (instead of 2020 add 2021) like below and press enter:
Calendar = CALENDAR(DATE(2016,1,1),DATE(2021,12,31))
4- revert back the above calculated columns to the original formulas.
v-kelly-msft
Community Support
Community SupportHi Reilstone78 ,
The calendar table is a created one or a default one in the app?
As tested here,it contains more dates than just end of 2020/12/31:
Below is a reference may be of help:
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi Kelly,
The Calendar is the default one in the App.
I suppose I could drop this table and re-create my own calendar and rebuild the relationships. Sounds like a lot of work though!
I've logged a ticket with MS.
Thanks
- v-kelly-msft
Hi Reilstone78 ,
Is your issue solved now?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!