How to use Time Intelligence when you only have year and quarter

[Anonymous]

Hi,

I have to build up a dashboard where I show how things have change from one quarter to another. However, we are collecting the data quartely therefore, the only date data in my dataset are year and quarter. 

Is it possible to use Time Intelligence in this case since a don't have specific dates? 

Do you have any tips or resources that I should look at?

Thank You,

[Fowmy · Accepted solution]

@Matpel10

The date table is assumed to have continuous dates from the beginning to the end of the year to support the time intelligence function. However, if applicable, you can create a Year Quater Table and with a YYYYQQ and link key then use custom measurement without the use of time intelligence.

________________________

If my answer was helpful, consider Accepting it as the solution to help other members find it

Click the Thumbs-Up icon if you like this answer 🙂

Youtube Linkedin

[edhans · Accepted solution]

Yes @Anonymous . I generally recomend you convert those times to actual dates. I usually recommend using the quarter end or year end. This is easy to do with Power Query. My data in this example only contains the first two columns, Month and Year.

From there, I use Date.FromText and the Date.EndOfMonth, Date.EndOfQuarter, or Date.EndOfYear functions to create whatever date I need.

See this M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMjIwMlCK1YlWcktNQub6JhbBubEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t]),
    #"Added Month End" = Table.AddColumn(Source, "Month End", each Date.EndOfMonth(Date.FromText([Month] & " 1, " & [Year]))),
    #"Added Quarter End" = Table.AddColumn(#"Added Month End", "Quarter End", each Date.EndOfQuarter(Date.FromText([Month] & " 1, " & [Year]))),
    #"Added Custom" = Table.AddColumn(#"Added Quarter End", "Year End", each Date.EndOfYear(Date.FromText([Month] & " 1, " & [Year]))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Month End", type date}, {"Quarter End", type date}, {"Year End", type date}})
in
    #"Changed Type"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If your data is in a different format, and you are having trouble getting it to convert to a date, provide some sample data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Fowmy · Accepted solution]

@Matpel10

The date table is assumed to have continuous dates from the beginning to the end of the year to support the time intelligence function. However, if applicable, you can create a Year Quater Table and with a YYYYQQ and link key then use custom measurement without the use of time intelligence.

________________________

If my answer was helpful, consider Accepting it as the solution to help other members find it

Click the Thumbs-Up icon if you like this answer 🙂

Youtube Linkedin