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.
Hello Anonymous and thank you for sharing the workaround! 🙂
However, i have one question. I am not sure if i missed a step or if it is just not possible: i have updated the calendar table, then published the report. It has been now refreshed and when i have downloaded the "refreshed" pbix, calendar table is back to 2020. Do i have to do the workaround for each refresh until MS provides an accurate table, or did i miss something?! Thank you for your help.