Orchestrating the data on time-based characteristics is an important task while you are analyzing the progress of your work. While we are dealing with the time characteristics, the Data Time Tool will be handy to deal with different date formats.

Let say we have two data sets which have a daily sales order data in every store and a monthly target to achieve in each store. We need a daily report which will have the target that needs to be met in the remaining days of the month.
finding-current-progress-towards-target

 

In this case, we will be using Running Total Tool, to find the progress of the actual sales from which we can calculate the current target after combining these two tables using the Join Tool.

Solution

First, we need to generate a common field based on the date(In this case month field in the same format) in both tables.
finding-current-progress-towards-target

After creating month fields in the same format, we can join the tables with the join tool using the below configuration.
finding-current-progress-towards-target

After joining the tables, we need to use the running total tool to find the amount we are receiving at the end of each sales order as below.
finding-current-progress-towards-target

After this, we can use the sort tool to sort the data based on order date. We can use the formula tool to calculate the current target after each sales order as shown below in the workflow.finding-current-progress-towards-target

The select tool is used to remove the unwanted fields in the output data file, in this case, we have removed the calculated field, running total, from the output and generate the output as below.
finding-current-progress-towards-target

Using this similar method, we can combine various data sets with different date formats and create structured data for reporting.

 

Read more about similar Self Service BI topic here and learn more about Visual BI Solutions Microsoft Power BI offerings here. 

Subscribe to our Newsletter

5920 Windhaven Pkwy
Plano TX 75093.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com