Microsoft 365 Usage Analytics PBI report issue for 2021

[Reilstone78]

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 

[CG_pbi]

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.

[Anonymous · Accepted solution]

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]

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-wor...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Reilstone78]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[daniele_tiles]

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.

[Anonymous]

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.

[Reilstone78]

Hi PBI Community.

Has anybody got any advice on this?

Thanks in advance,

Wesley 

[daniele_tiles]

+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]

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.