The ParallelPeriod is a DAX Time Intelligence function of Power BI that gives you the ability to get the Parallel period to the Current period. You can navigate to periods in the past or future. The ParallelPeriod gives the result of a period parallel to this period (in the past or future), which is statically determined in the Interval parameter; can be Month, Quarter, or Year.
Syntax:
ParllelPeriod(<date field>, <number of intervals>, <interval>)
You can choose the interval to be Month, Quarter, or Year. and the number of intervals can be negative (to go to past), or positive (to go to the future).
Example:
Suppose we have a Data Model as follows.
In the above Model, we have a Date table "tbl_Calendar" with the dates between 01/01/2010 to 31/12/2020 :
MinDate = MIN(tbl_Calendar[cDate])
MaxDate = MAX('ParallelPeriod'[cDate])
Now lets discuss about how the PARALLELPERIOD function works in various Scenarios.
Scenario 1 : PARALLELPERIOD with -1 Year interval:
In the following case, the function returns all the available Dates from the first available Month and Year of the tbl_Calendar to till last available Date of 1 Year before the last Year(2020) of the Calendar table.
ParallelPeriod =
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, YEAR)
)
Result:
MaxDate = MIN('ParallelPeriod'[cDate])
MaxDate = MAX('ParallelPeriod'[cDate])
Scenario 2 : PARALLELPERIOD with -1 Year interval
Case1: (with =Specified Date)
In the following case, it returns all the Dates from the first available date of the Previous Year of 2015. Which means, it returns all the available Dates for the Year 2014.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, YEAR),
tbl_Calendar[cDate]=vBaseDate
)
Result:
Case2: (with <=Specified Date)
In the following case, it returns all the Dates from the first available Date of the first Year in the Calendar table to the last date of the Previous Year of 2015. Which means, it returns all Dates from the available start date of 2010 to last available date of 2014.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, YEAR),
tbl_Calendar[cDate]<=vBaseDate
)
Result:
Case3:(with >=Specified Date)
In the following case, it returns all available Dates from the Previous Year of 2015 till the last Date of the Last available Year-1 in the Calendar table. Which means, it returns all Dates from the available start Date of 2014 to last available Date of 2019.ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, YEAR),
tbl_Calendar[cDate]>=vBaseDate
)
Result:
Scenario 3 : PARALLELPERIOD with 0 Year interval:
In the below Case, it returns all available Dates for the Year of the given vBaseDate.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],0, Year),
tbl_Calendar[cDate]=vBaseDate
)
Result:
-------------------------------------------------------------------------------------------------
Scenario 4 : PARALLELPERIOD with +1 Year interval:
In the below Case, it returns all available Dates for the Next Year of the given vBaseDate.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],1, Year),
tbl_Calendar[cDate] = vBaseDate
)
Result:
-------------------------------------------------------------------------------------------------
Scenario 5 : PARALLELPERIOD with -1 Quarter intervalCase1: (with =Specified Date)
In the following Case, it returns all Dates for Previous Quarter that falls before the vBaseDate.ParallelPeriod =
VAR vBaseDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, Quarter),
tbl_Calendar[cDate]=vBaseDate
)
Result:
Case2:(with =Specified Date)
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, Quarter),
tbl_Calendar[cDate]=vBaseDate
)
Result:
Case3:(with >=Specified Date)
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, Quarter),
tbl_Calendar[cDate]>=vBaseDate
)
Result:
Scenario 6 : PARALLELPERIOD with 0 Quarter interval
In the following Case, it returns all Dates for Current Quarter that falls in the vBaseDate.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],0, Quarter),
tbl_Calendar[cDate] = vBaseDate
)
Result:
-------------------------------------------------------------------------------------------------
Scenario 7 : PARALLELPERIOD with +1 Quarter interval
Case1: (with =Specified Date)
In the following Case, it returns all Dates for Next Quarter of the given vBaseDate.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],1, Quarter),
tbl_Calendar[cDate] = vBaseDate
)
Result:
Case2: (with <=Specified Date)
In the following Case, it returns all available Dates from Previous Quarter (Apr-Jun) based on vBaseDate, in the first available Year(2010) of the Calendar till the last available date of the Year of vBaseDate. This means the all available Dates from Apr'2010 to Dec2015.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],1, Quarter),
tbl_Calendar[cDate] <= vBaseDate
)
Result:
Case3: (with >=Specified Date)
In the following Case, it returns all available Dates from Next Quarter (Oct-Dec) of the given vBaseDate, till the last available date of the Year of vBaseDate. This means the all available Dates from Oct2015 to Dec2020.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],1, Quarter),
tbl_Calendar[cDate] >= vBaseDate
)
Result:
Scenario 8 : PARALLELPERIOD with -1 Month interval
Case1: (with =Specified Date)
In below Case, it returns all available Dates for Previous Month that falls before vBaseDate.ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, Month),
tbl_Calendar[cDate]=vBaseDate
)
Result:
Case2: (with <=Specified Date)
In below Case, it returns all available Dates from first Month and Year in Calendar table till the Previous Month that falls before vBaseDate.ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, Month),
tbl_Calendar[cDate]<=vBaseDate
)
Result:
Case3: (with >=Specified Date)
In below Case, it returns all available Dates from Previous Month that falls before vBaseDate till the Previous month of Last available Month and Year in the Calendar.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, Month),
tbl_Calendar[cDate]>=vBaseDate
)
Result:
Scenario 9 : PARALLELPERIOD with 0 Month interval
In below Case, it returns all available Dates for Current Month based on the vBaseDate.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],0, Month),
tbl_Calendar[cDate]=vBaseDate
)
Result:
-------------------------------------------------------------------------------------------------
Scenario 10 : PARALLELPERIOD with +1 Month interval
Case1: (with =Specified Date)
In below Case, it returns all available Dates for Next Month based on the vBaseDate.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],+1, Month),
tbl_Calendar[cDate]=vBaseDate
)
Result:
Case2: (with <=Specified Date)
In below Case, it returns all available Dates from Next Month to the first available Month and Year of Calendar till the Next Month based on the vBaseDate. This means all available Dates from Feb2010 to Aug2015.
In below Case, it returns all available Dates from Next Month to the first available Month and Year of Calendar till the Next Month based on the vBaseDate. This means all available Dates from Feb2010 to Aug2015.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],+1, Month),
tbl_Calendar[cDate]<=vBaseDate
)
Result:
Case3: (with >=Specified Date)
In below Case, it returns all available Dates from Next Month based on the vBaseDate to the last available Month and Year of Calendar. This means all available Dates from Aug2015 to Dec2020.
In below Case, it returns all available Dates from Next Month based on the vBaseDate to the last available Month and Year of Calendar. This means all available Dates from Aug2015 to Dec2020.
ParallelPeriod =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],+1, Month),
tbl_Calendar[cDate]>=vBaseDate
)
Result:
-------------------------------------------------------------------------------------------------
Scenario 11 : PARALLELPERIOD with -1 Month interval
Case1: (with = OR(Specified Date1,Specified Date2))
In below Case, it returns all available Dates from Previous Month of vBaseDate to Previous Month of vBaseDate2.ParallelPeriod =
VAR vBaseDate1 = DATE(2015, 07, 15)
VAR vBaseDate2 = DATE(2015, 09, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, Month),
OR(
tbl_Calendar[cDate] = vBaseDate1,
tbl_Calendar[cDate] = vBaseDate2
)
)
-------------------- OR --------------------
ParallelPeriod =
VAR vBaseDate1 = DATE(2015, 07, 15)
VAR vBaseDate2 = DATE(2015, 09, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, Month),
AND(
tbl_Calendar[cDate] >= vBaseDate1,
tbl_Calendar[cDate] <= vBaseDate2
)
)
Result:
Case2: (with OR(>=Specified Date1, <=Specified Date2))
In below Case, it returns all available Dates from First Month of Calendar to Previous Month of the last available Month and Year of the Calendar.ParallelPeriod =
VAR vBaseDate1 = DATE(2015, 07, 15)
VAR vBaseDate2 = DATE(2015, 09, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],-1, Month),
OR(
tbl_Calendar[cDate] >= vBaseDate1,
tbl_Calendar[cDate] <= vBaseDate2
)
)
Result:
-------------------------------------------------------------------------------------------------
Scenario 12 : PARALLELPERIOD with 0 Month interval
In below Case, it returns all available Dates from Current Month of vBaseDate to the Current Month of vBaseDate2.
ParallelPeriod =
VAR vBaseDate1 = DATE(2015, 07, 15)
VAR vBaseDate2 = DATE(2015, 09, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],0, Month),
OR(
tbl_Calendar[cDate] = vBaseDate1,
tbl_Calendar[cDate] = vBaseDate2
)
)
Result:
Scenario 13 : PARALLELPERIOD with 1 Month interval
Case1: (with = OR(Specified Date1,Specified Date2))
In below Case, it returns all available Dates from Next Month of vBaseDate to the Next Month of vBaseDate2.ParallelPeriod =
VAR vBaseDate1 = DATE(2015, 07, 15)
VAR vBaseDate2 = DATE(2015, 09, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],1, Month),
OR(
tbl_Calendar[cDate] = vBaseDate1,
tbl_Calendar[cDate] = vBaseDate2
)
)
-------------------- OR --------------------
ParallelPeriod =
VAR vBaseDate1 = DATE(2015, 07, 15)
VAR vBaseDate2 = DATE(2015, 09, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],1, Month),
AND(
tbl_Calendar[cDate] >= vBaseDate1,
tbl_Calendar[cDate] <= vBaseDate2
)
)
Result:
Case2: (with OR(>=Specified Date1, <=Specified Date2))
In below Case, it returns all available Dates from Next Month to the first available Month and Year of Calendar till the Last available Month and Year of the Calendar. This means it returns all available Dates from Feb2010 to Dec2020.ParallelPeriod =
VAR vBaseDate1 = DATE(2015, 07, 15)
VAR vBaseDate2 = DATE(2015, 09, 15)
RETURN
CALCULATETABLE(
PARALLELPERIOD(tbl_Calendar[cDate],1, Month),
OR(
tbl_Calendar[cDate] >= vBaseDate1,
tbl_Calendar[cDate] <= vBaseDate2
)
)
Result:
The PARALLELPERIOD(tbl_Calendar[cDate],0, Month) is logically equivalent to:
PARALLELPERIOD( CALCULATETABLE(DISTINCT(tbl_Calendar[cDate]) ),0, Month)
DATEADD vs PARALLELPERIOD:
The DATEADD works on the period dynamically (like SamePeriodLastYear), but the ParallelPeriod works statically on the interval mentioned as the parameter.
The DateAdd Function can work on an interval of Day, Month, Quarter, or Year. The ParallelPeriod function only works on intervales of Month, Quarter, and Year.--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.