Microsoft 365 usage analytics - create query in desktop to get past years of license trends

[smmoore34]

I've been connected to the PBIT file and have been using this to see my monthly trends. The report by default, shows trends in licencing over the past 12 months.

I've been asked to show the same trends but over the past 3 years. I found the table, (see screenshot) but it only includes data from the past 12 months. 

I'd like to create a query with the same information but over the past 3 years. I'd appreciate any help!

[MmmVvv_23]

Hello,

Why I am having empty sheet for Users in this Report?
I am Power BI Admin (we have PRO environment) and can manage many things in Power BI Service but I am not able to see the users here (nor the Products). Any idea why?

[v-yifanw-msft · Accepted solution]

Hi @smmoore34 ,

Based on the information you have provided, to create a query containing data for the past 3 years, you will need to modify the query parameters or create a new query to explicitly request data for the desired time period. The following are ways to help you achieve this:

1. If the current query uses parameters to define the date range, you can adjust those parameters to extend the date range to cover the past 3 years.
2. If it is not feasible to modify the existing parameters, or if you wish to leave the original query unchanged, you can create a new query. Use the same tables and joins as the original query, but specify a wider date range. Make sure your query filters the data to include the last 3 years. For guidelines on creating queries, you can read the following documentation:
   Query using the visual query editor - Microsoft Fabric | Microsoft Learn
3. Ensure that the underlying data source contains data from the past 3 years. If the data only goes back 12 months, you may need to adjust your data retention policy or find an alternative data source that contains the historical data you need.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smmoore34]

Thank you. Upon further investigation I see the data source does not include 3 years worth of data. Only the past 12 months.