Transforming dates

1. Transforming dates

In this scenario, we work in the finance department of a global grocery retail company. We have received a dataset that details credit card transactions across our global stores, and we need to understand more about our store performance. Looking at the data, we can see that we have basic information about each transaction. We have the time it occurred, the product in question, the price of each item, and the number purchased. Before analyzing this information, we need to transform our timestamp into a human-readable format. Currently, we have this value in UNIX time, which is common for some high-velocity data sources. I will first convert this into ISO standard by comparing the value to the date January 1st, 1970 using simple addition.. timestamp_readable = var UnixTimestamp = grocery[timestamp] / (60 * 60 * 24 * 1000) return DATE(1970,1,1) + UnixTimestamp Now I have a human-readable DateTime version of the timestamp that I can do some work with. Double-checking the locale for my new date field, I can see that it has defaulted to English (United States) due to my Power BI default settings. Still, I could always adjust this depending on who my intended audience was. Let’s continue in our analysis, and imagine that we want to analyze how different product groups are doing on a quarterly basis. We can utilize some of the time intelligence capabilities that Power BI has natively to accomplish this. To aggregate our dataset, we will use the SUMMARIZE function and will aggregate across both the quarter and category dimensions. From there, we will create a variety of metrics for our analysis. grocery quarterly = SUMMARIZE ( grocery, grocery[timestamp_readable].[Quarter], grocery[product category], "Average Price", AVERAGE ( grocery[unit_price] ), "Total Sold", SUM ( grocery[count] ) ) Notice how when we select our timestamp field, Power BI can understand that it is a date-time field and has various components we can break down into. Now we have a variety of data points to analyze. Let’s create a straightforward horizontal bar chart where we can drill down into the number sold in each category by quarter. After diving down into each quarter, we can see that wine is by far our biggest category from a unit's perspective, and we need to keep up that good performance.

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.