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 data. The refresh completes successfully. The Master Date Calendar query ends on the 31/12/2020

Calendar = CALENDAR(DATE(2016,1,1),DATE(2020,12,31))

When I try to extend the table I get a USERRELATIONSHIP FUNCTION CAN ONLY USE THE TWO COLUMNS AND A CIRCULAR DEPENDENCY WAS DETECTED.

I can see 2021 data in the main query tables.

Has anybody else encountered this issue and if so, how can I resolve it?

Kind Regards,

Wesley

Need Help

Anonymous
5 years ago
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.

10 Replies

daniele_tiles
Advocate II
5 years ago
+1. We've been using this report, and discovered today the same problem (we cannot update, we get the same error).
We tried to donwload again the PBIT from the server, hoping for a new version, but it's still with the calendar until 31/12/2020, and we get the aforementioned error.
- TinaL22
  Frequent Visitor
  5 years ago
  Hi daniele_tiles,
  I have exactly the same issue, the date ends on 2020-12-31, I am not sure is a Microsoft issue? or we have to fix it on our end, and there is no option for me to download the pdix.
  
  Thanks a lot.
Anonymous
Not applicable
5 years ago
Experiencing the same. I have customized reports which are also not updating for Jan 2021. Normally have to wait 3+ days into the next month before aggregated data of the previous month is updated and just discovered this yesterday.
Anonymous
Not applicable
5 years ago
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.
Reilstone78
Regular Visitor
5 years ago
Hi PBI Community.

Has anybody got any advice on this?

Thanks in advance,

Wesley
v-kelly-msft
Community Support
5 years ago
Hi 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:

https://docs.microsoft.com/en-us/microsoft-365/admin/usage-analytics/customize-reports?view=o365-worldwide

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!
- Reilstone78
  Regular Visitor
  5 years ago
  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
    Community Support
    5 years ago
    Hi Reilstone78 ,
    
    Is your issue solved now?
    
    Best Regards,
    Kelly
    
    Did I answer your question? Mark my post as a solution!
- daniele_tiles
  Advocate II
  5 years ago
  Hi,
  the calendar is created inside by CALENDAR function of PBI.
  The point is that the calendar isn't updatable, for the errors reported above.
  I guess the work-around could be to remove the filters all around the template, in order to get the latest version of the data, but it's complex and a bit of a PITA...
  Since this is an official Microsoft PBI Template, I hope we will receive an official response.
CG_pbi
Frequent Visitor
5 years ago
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.

Forum Discussion

Microsoft 365 Usage Analytics PBI report issue for 2021

10 Replies

Related content

Introduction to Microsoft Fabric: A Unified Analytics Platform

Microsoft 365 Usage Analytics App broken

Microsoft 365 Usage Analytics Report Refresh fails in the Service

Microsoft Power BI Report Server January 2026 latest Release

Microsoft 365 Usage Analytics - data are not showing in dashboard for 2026