CODEWITHSUNDEEP
azurepowerbipowerquerym
Displaza
Displaza

Reputation: 77

PowerBI Azure Cost Management API timeperiod current day to x

I've called the Azure consumption API in PowerBI to get up to date information on resource costs etc. However there is the issue of having long term up to date information, where instead of having just the previous month to this date, I would like the last year to this date. Ideally I want to get data from the current date to a date that the cloud infrastructure was started but for now a year will do.

The issue I'm having is a type issue, or in PowerBI it is called a DataFormat error. The error says "We couldn't convert to date" however my code has converted the date to text via the Date.ToText() function. The body of the request looks like this:

currentdate = Date.ToText(Date.From(DateTime.LocalNow)),
body = 
    "{
        ""type"": ""Usage"",
        ""timeframe"":""Custom"",
        ""timeperiod"": {
            ""from"" : ""2023-03-01T00:00:00+00:00""
            ""to"": """& currentdate & """,
        },
        ""dataset"": {
            ""aggregation"": {
                ""totalCost"": {
                    ""name"": ""Cost"",
                    ""function"": ""Sum""
                }
            },
            ""granularity"" : ""Daily"",
            ""grouping"": [
            {
                ""type"": ""Dimension"",
                ""name"": ""ResourceGroup""
            },
            {
                ""type"": ""Dimension"",
                ""name"": ""ServiceName""
            }
            ]
        }
    }"

The code I'm focusing on is the "timeframe" key pair. The "from" is set to the 1st of March 2023, and the "to" is supposed to be set to whatever the current date is.

Does anyone know how to get the script to recognise that "currentdate" has been converted to text?

Upvotes: 0

Views: 90

Answers (1)

horseyride
horseyride

Reputation: 21318

This doesn't generate any result :

currentdate = Date.ToText(Date.From(DateTime.LocalNow))

This would generate text like 3/28/2024 :

currentdate = Date.ToText(DateTime.Date(DateTime.LocalNow()))

This gives 2024-03-28 :

currentdate = Date.ToText(DateTime.Date(DateTime.LocalNow()), [Format="yyyy-MM-dd"])

And finally this gives 2024-03-28T00:00:00+00:00 which is probably what you want based on the format of the other date field :

currentdate =  Date.ToText(DateTime.Date(DateTime.LocalNow()), [Format="yyyy-MM-dd"])&"T00:00:00+00:00"

Upvotes: 1

Related Questions