cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reilstone78
Regular Visitor

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 

1 ACCEPTED SOLUTION
aleem
Frequent Visitor

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.

View solution in original post

10 REPLIES 10
CG_pbi
Frequent Visitor

Hello @aleem 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.

aleem
Frequent Visitor

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 Support

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:

v-kelly-msft_1-1612951053756.png

 

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!

 

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

Hi  @Reilstone78 ,

 

Is your issue solved now?

 

Best Regards,
Kelly

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

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.

aleem
Frequent Visitor

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
Regular Visitor

Hi PBI Community.

 

Has anybody got any advice on this?


Thanks in advance,

 

Wesley 

daniele_tiles
Advocate I
Advocate I

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

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.

pbix.PNG

Thanks a lot.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!