What is the Power BI Parallelperiod function?
The Power BI Parallelperiod is a time intelligence function that returns a table containing a column of dates that represents a period that is parallel to the dates provided in the dates column as input to the function and shifts the dates to many intervals either forward or backward in time in the current context.
This function accepts the current set of dates in the column specified by dates, shifts the first date and the last date to the specified number of intervals, and then returns all contiguous dates between the two shifted dates. If the interval is a partial range of month, quarter, or year, then any partial months in the result are also filled out to complete the entire interval.
Table of contents
Key Takeaways
- The Power BI Parallelperiod function is used to shift a range of dates forward or backward by a specified number of intervals, returning all contiguous dates between the shifted dates.
- This function accepts three inputs, i.e., dates, number_of_intervals, and intervals, and provides a result table containing dates.
- You can use the Power BI Parallelperiod function to shift the range of dates for your reporting or visualization requirements.
- Consider the limitations before you recommend using this function in your Power BI calculations or measures.
Syntax
The syntax for the Power BI PARALLELPERIOD function is outlined below:
PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)
It accepts three parameters that include:
| Parameter Name | Mandatory/Optional | Description |
|---|---|---|
| Dates | Mandatory | This parameter references a column containing dates. It can be one of the followingA reference to date or time columnA table expression returning a single column of date or time valuesA Boolean expression defining a single-column table of date or time values |
| Number_of_intervals | Mandatory | A numeric value that provides the number of intervals to be added or subtracted from the date parameter |
| Interval | Mandatory | The interval by which to shift the dates. The value for this parameter can be a year, quarter, month, etc. |
You can use Power BI parallelperiod month, year, or quarter to create a date dataset.
How to use the Power BI Parallelperiod function?
To use Power BI Parallelperiod, follow the steps highlighted below:
Step 1: Launch Power BI Desktop in your system.
Step 2: Import the dataset into Power BI using the Get data option and load it into Power BI using the Load button.
Step 3: Navigate to the Modeling tab and click on the New table option.
Step 4: Create a Calendar table using the dates in your dataset. Save the changes by clicking on Enter.
Calendar Table = CALENDAR(MIN(financials[Date]), MAX(financials[Date]))
Step 5: Navigate to Model view to establish a relationship between financials and Calendar Table.
Step 6: Navigate to the Modeling tab and click on the New measure option.
Step 7: Write the DAX expression in the formula bar to create a measure for your reporting requirements.
Sales in Prev Period = CALCULATE(SUM(financials[ Sales]), PARALLELPERIOD(‘Calendar Table'[Date],-1,QUARTER))
Click on the Commit icon to save the changes.
Step 8: Navigate to the Visualizations pane and choose the Table visual icon. Drag and drop the measure to the report canvas or use it as part of your calculations.
It will create a Table visual where you can view the current sales and the previous quarter’s sales in the same visual.
Note: Power BI parallelperiod week and Power BI parallelperiod day are currently not supported in the Power BI Parallelperiod function. You can only specify Year, Quarter, and Month. If you observe Power BI Parallelperiod not working, try troubleshooting options such as creating a calendar table, ensuring the date column is used, and maintaining the syntax of the function.
Examples
In this section, we will go through a few examples demonstrating the use of Prallelperiod function in Power BI.
Example #1
In this example, we will demonstrate the Power BI Parallelperiod to display the Sales in the Previous period using the NFT_Sales dataset. The NFT_Sales dataset contains the historical NFT sales around the world.
To use the Power BI Parallelperiod with filter function, follow the instructions below:
Step 1: Import the dataset into Power BI using the Get data option and click on the Load button to load it into Power BI.
Step 2: Navigate to the Modeling tab and click on the New table option.
Step 3: Create a Calendar table using the dates in your dataset. Save the changes by clicking on Enter.
Calendar NFT = CALENDAR(MIN(NFT_Sales[Date]), MAX(NFT_Sales[Date]))
Step 4: Navigate to Model view to establish a relationship between NFT_Sales and Calendar NFT Table.
Step 5: Navigate to the Data pane. Select the table, right-click on it, and choose New measure from the context menu.
Step 6: In the formula bar, write the DAX expression to create a new measure for the Previous month’s sales using Power BI Parallelperiod month.
Sales Previous Month in USD = CALCULATE(SUM(NFT_Sales[Sales_USD]),PARALLELPERIOD(‘Calendar NFT'[Date],-1,MONTH))
Click on the Commit icon to save the changes. It will create a measure for the chocolate table.
Step 7: Navigate to the Visualizations pane and select the Table visual icon. Map the newly created measure to this card visual.
Once the mapping is completed, you will see the data visualization in the report canvas as provided below:
Example #2
In this example, we will demonstrate the Power BI ParallelPeriod to display Previous period automobile orders using the Automobile Sales Historical data dataset. The Automobile Sales Historical data dataset contains information on various automobile prices along with the details on different cars, bikes, etc., over a while.
To use the Power BI Parallelperiod, follow the instructions below:
Step 1: Import the dataset into Power BI using the Get data option and click on the Load button to load it into Power BI.
Step 2: Follow the steps highlighted in Example 1 to create a Calendar Automobile table using the date field of the Automobile Sales Historical data dataset.
Calendar Automobile = CALENDAR(MIN(‘Automobile Sales Historical data'[ORDERDATE]),MAX(‘Automobile Sales Historical data'[ORDERDATE]))
Establish the relationship between Automobile Sales Historical data and the Calendar Automobile tables.
Step 3: Navigate to the Data pane. Select the table, right-click on it, and choose New measure from the context menu.
Step 4: In the formula bar, write the DAX expression to create a new measure.
QuantityOrdered in Previous Quarter = CALCULATE(SUM(‘Automobile Sales Historical data'[QUANTITYORDERED]),PARALLELPERIOD(‘Calendar Automobile'[Date],-1,QUARTER))
Click on the Commit icon to save the changes.
Navigate to the Visualizations pane and select the Clustered column chart visual icon. Map the newly created measure to this card visual.
Once the mapping is completed, you will see the data visualization in the report canvas as provided below.
Example #3
In this example, we will demonstrate the Power BI Parallelperiod with a date filter using the SpaceX Mission Historical data dataset. The SpaceX Mission Historical data dataset contains the historical dataset of various flight launches, including the vehicle details.
To use the Power BI Parallelperiod function with a date filter, follow the instructions below:
Step 1: Import the dataset into Power BI using the Get data option and click on the Load button to load it into Power BI.
Step 2: Follow the steps highlighted in Example 1 to create a Calendar SpaceX table
Calendar SpaceX = CALENDAR(MIN(‘SpaceX Missions Historical data'[Launch Date]),MAX(‘SpaceX Missions Historical data'[Launch Date]))
Establish the relationship between SpaceX Mission Historical data and the Calendar SpaceX tables.
Step 3: Navigate to the Data pane. Select the table, right-click on it, and choose New measure from the context menu.
Step 4: In the formula bar, write the DAX expression to create two new measures.
Total Launches = COUNT(‘SpaceX Missions Historical data'[Flight Number])
Total Launches in Previous Quarter = CALCULATE(COUNT(‘SpaceX Missions Historical data'[Flight Number]), PARALLELPERIOD(‘Calendar SpaceX'[Date], -1, QUARTER))
Step 5: Navigate to the Visualizations pane and select the Line and stacked column chart visual icon. Map the newly created measure to this card visual.
Once the mapping is completed, you will see the data visualization in the report canvas as provided below
Important Things to Note
- Power BI Parallelperiod shifts the dates forward in time or backward depending on values specified in number_of_intervals being positive or negative.
- The interval parameter provided in Power BI Parallelperiod is an enum and not a set of string values. Hence, avoid using the values enclosed in quotation marks and ensure the values are spelled in full such as year, quarter, month, etc.
- The output of Power BI Parallelperiod is a table of dates that appear in the values of the underlying table column.
- Power Bi Parallelperiod function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Frequently Asked Questions (FAQs)
The key differences between DATEADD and PARALLELPERIOD in Power BI is outlined below:
DATEADD:
• DATEADD function can be used to add or subtract a specified number of intervals (say days, months, years, etc.) to a given date.
• This function returns partial periods at the given granularity level.
• For example, if you have selected a range of dates that starts from Feb 5 to Feb 16, and you want to shift it by a month, then DATEADD will include dates from Mar 5 to Mar 16.
PARALLELPERIOD:
• PARALLELPERIOD function can be used to shift the first date and the last date in the specified number of intervals and then return all contiguous dates between the two shifted dates.
• This function always returns the full period at the given granularity level.
• For example, if you have selected a range of dates that starts from Feb 5 to Feb 16, and you want to shift it by a month, then DATEADD will include all the dates from Mar 1 to Mar 31.
Missing data or irregular intervals can create issues if you are using PARALLELPERIOD in Power BI such as being unable to find the corresponding dates in a similar previous period. To mitigate such issues, you can try some of the options outlined below:
• Consider creating a calendar table that contains all the required date ranges. Establish a relationship between your dataset and the calendar table to ensure that when you use the PARALLELPERIOD function on the date column of the calendar table, it accurately returns the full periods at the given granularity level
• Use a time intelligence function that returns the similar dates shifted by a year as per current selected data.
• Evaluate using a DAX expression for calculating the previous period dates using the data column in the source table and use it as a filter expression in the CALCULATE function in Power BI.
Yes, you can nest PARALLELPERIOD within other DAX functions. By doing so, you can create advanced and complex time intelligence calculations. However, consider the performance consequences before using the nest PARALLELPERIOD function.
The PARALLELPERIOD function in Power BI does not support custom calendars or fiscal years by default. However, you can create a custom calendar table containing all the dates and fiscal years and establishing a relationship between your dataset and the calendar table.
Recommended Articles
This has been a guide to Power BI Parallelperiod. Here we explain how to use it in Power BI, with examples, and points to remember. You may learn more from the following articles –
Leave a Reply