Cosmos DB to PowerBI connection

[khush19]

I have a requirement where i need to Use COSMOS DB to get the current and historica data to POWERBI

1. How to write query in POWER BI to get like today data or last week data comparing mt date field in COSMOS DB?
2. What I uderstood is as powerBI support only IMPORT fuctionality to COSMOS DB,and we can schedule refresh after every hour which is minimum,My PowerBI report will get embedded to POWERBI embedded .
   •  Is is possible that every time I Load/reload  my page then powerBI should query COSMOS Db and get updated data and if the user is on same page for more than a hour the page get refreshed ,by the latest data through schedule refresh.
3. I am POWERBI pro licence user,I read that I can schedule refresh ,where minimum is hourly for import,https://community.powerbi.com/t5/Service/Azure-DocumentDB-and-slow-refresh/m-p/47940#M10390 BUT when i see on my POWER BI service,I can see only Daily ,weekly ..no Hourly 

[v-lili6-msft]

hi @khush19 

1. You could write custom SQL code to get data or use custom filter in edit queries

2. You could set schedule refresh for dataset, dataset will Load/reload data from datasource. The reports refresh data cache from dataset.

3. For pro licence user, they can schedule refresh 8 times a day at most, You could choose daily or weekly, then choose 8 time points at most for refresh.

Regards,

Lin

[khush19]

Thanks Lin,

In COSMOSDB is it possible to get lastweek data will current TIME.

I hav documents present in COSMOS DB ,in that Document I have JSON Array structure

Order{

"Amount":$10,

"OrderDate"="2019-11-15 10:55:49.000"

          }

So i need sum for today and also sum for last week same day till current time:

So if today is 15/11 2:36 pm

So i need Amount for today till 2:56 and also amout for 08/11 till 2.56,is this possibe?

[v-lili6-msft · Accepted solution]

hi @khush19 

Yes, you could try this way as below:

Step1:

In Edit Queries, add a days-between column as below:

DateTime.LocalNow()-[OrderDate]

Step2:

Change the datatype to decimal number

Step3:

Now add a conditional column as below:

if [Days difference]>=0 and [Days difference]<=1 then "last days" else "Not last day"

Note:This is my simple sample data, For your case, just use 7 instead of 1.

Step3:

Filter this column which is "Last days"

Step4(optional):

remove these new columns.

and here is my simple sample pbix file, please try it.

Regards,

Lin

[khush19]

Hi Lin,

Just a quick question ,I tried to get sum or any Aggregate function in COSMOSDB through POWERBI,but it always give me error:

The field "errors" of record was not found.

Can we perform the aggregation in powerbi for COSMOS?

and as you mentioned edit in Edit query mode,so  when i edit in POWERBI to give me today data ,it takes a long time to filter because it has loaded all the document and then filtering on that.

can we write something like getdate() in sql query..where orderdate=getdate() in powerbi for cosmos and also get last week data through writing query?