Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

PARALLELPERIOD Filter Problem

Hello,

 

I have a table which shows a value for the current month and a value for the previous month.  Take for example here:

 

Month           Input Plant.Actual

June 2017     500

May 2017     100

 

I've created a date table with a column which marks "Is Current" if the month in the date table is current.  The date table joins to the example table I've pasted above.

 

In a Matrix visual, I want to have the information available in the following format:

 

Current Month     Prevoius Month

500                       100

 

For the previous month calculation, I've used the following formula:

 

Previous Month =
IF(
 ISFILTERED('DimDate'[Calendar Date]),
 ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
 VAR __PREV_MONTH =
  CALCULATE(
   SUM('Input Plant - Modified'[Actual]),
   PARALLELPERIOD('DimDate'[Calendar Date].[Date], -1, MONTH)
  )
 RETURN
  __PREV_MONTH  
)

 

I will get the proper results if I filter for June in the visual, but if I filter the visual for my column where I mention "Is Current", it filters the data so only June shows, but May will just be the same value as June.  I believe this is happening because the filter is filtering the data so only June shows up and then the PARALLELPERIOD function which is trying to find the previous month's value finds no previous month available, since it's filtered out.  Is there any way to get around this issue and be able to filter and still have a function that pulls the previous month's value?

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

It works just fine for me.  Please see the image (notice that i have select Current month as yes in the the filter pane).  These are the measures that i used

 

Current month = SUM(Data[Actual])
Previous month = CALCULATE([Current month],PREVIOUSMONTH('calendar'[Date]))

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

 

It works just fine for me.  Please see the image (notice that i have select Current month as yes in the the filter pane).  These are the measures that i used

 

Current month = SUM(Data[Actual])
Previous month = CALCULATE([Current month],PREVIOUSMONTH('calendar'[Date]))

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I guess I should've explained the problem a little better.

 

Let's say we have two tables.  

 

Input Plant - Modified:

 

Date:               Actual:

1/1/2017         200

2/1/2017         300

3/1/2017         400

4/1/2017         600

5/1/2017         200

6/1/2017         800

7/1/2017         100

8/1/2017         300

9/1/2017         200

10/1/2017       600

11/1/2017       800

12/1/2017       400

 

 

DimDate:

 

Date:                         IsCurrentMonth:

1/1/2017                   Other Month

2/1/2017                   Other Month

3/1/2017                   Other Month

4/1/2017                   Other Month

5/1/2017                   Other Month

6/1/2017                   Current Month

7/1/2017                   Other Month

8/1/2017                   Other Month

9/1/2017                   Other Month

10/1/2017                 Other Month

11/1/2017                 Other Month

12/1/2017                 Other Month

 

These are joined by the date field in both tables.

 

In a matrix visual, I put "DimDate.Date" in the column field and "Input Plant - Modified.Actual" as the value.  Power BI will add it's own date heirarchy once I place the Date field in the column.  I'll remove everything except Month.  I can also add in my PREVIOUSMONTH or PARALLELPERIOD formula as a value for each month as a field named "Previous Month", and it should show the previous month's data in the Matrix.  Now, in the visual filter, I can filter for just June and it will show me June's value in the "Input Plant - Modified.Actual" field and May's value in the "Previous Month" field in the Matrix.  Great.

 

Here is the forumla for "Previous Month" again:

 

Previous Month =
IF(
 ISFILTERED('DimDate'[Calendar Date]),
 ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
 VAR __PREV_MONTH =
  CALCULATE(
   SUM('Input Plant - Modified'[Actual]),
   PARALLELPERIOD('DimDate'[Calendar Date].[Date], -1, MONTH)
  )
 RETURN
  __PREV_MONTH  
)

 

However, if I put in a page filter or a visual filter for the "IsCurrentMonth" field and filter for "Current Month" (which is June in this case), the "Previous Month" column will just show the same value as "Input Plant - Modified.Actual" which is June's value and not pull in May's value this time.

 

I believe this is happening because once I filter for "IsCurrentMonth" it just narrows down the "Input Plant - Modified" table for June and nothing else exists.  That's why the formula can't find May's value any more.  Is there any way to pull May's value when filtering for "Current Month" in this case?


@Anonymous wrote:

I guess I should've explained the problem a little better.

 

Let's say we have two tables.  

 

Input Plant - Modified:

 

Date:               Actual:

1/1/2017         200

2/1/2017         300

3/1/2017         400

4/1/2017         600

5/1/2017         200

6/1/2017         800

7/1/2017         100

8/1/2017         300

9/1/2017         200

10/1/2017       600

11/1/2017       800

12/1/2017       400

 

 

DimDate:

 

Date:                         IsCurrentMonth:

1/1/2017                   Other Month

2/1/2017                   Other Month

3/1/2017                   Other Month

4/1/2017                   Other Month

5/1/2017                   Other Month

6/1/2017                   Current Month

7/1/2017                   Other Month

8/1/2017                   Other Month

9/1/2017                   Other Month

10/1/2017                 Other Month

11/1/2017                 Other Month

12/1/2017                 Other Month

 

These are joined by the date field in both tables.

 

In a matrix visual, I put "DimDate.Date" in the column field and "Input Plant - Modified.Actual" as the value.  Power BI will add it's own date heirarchy once I place the Date field in the column.  I'll remove everything except Month.  I can also add in my PREVIOUSMONTH or PARALLELPERIOD formula as a value for each month as a field named "Previous Month", and it should show the previous month's data in the Matrix.  Now, in the visual filter, I can filter for just June and it will show me June's value in the "Input Plant - Modified.Actual" field and May's value in the "Previous Month" field in the Matrix.  Great.

 

Here is the forumla for "Previous Month" again:

 

Previous Month =
IF(
 ISFILTERED('DimDate'[Calendar Date]),
 ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
 VAR __PREV_MONTH =
  CALCULATE(
   SUM('Input Plant - Modified'[Actual]),
   PARALLELPERIOD('DimDate'[Calendar Date].[Date], -1, MONTH)
  )
 RETURN
  __PREV_MONTH  
)

 

However, if I put in a page filter or a visual filter for the "IsCurrentMonth" field and filter for "Current Month" (which is June in this case), the "Previous Month" column will just show the same value as "Input Plant - Modified.Actual" which is June's value and not pull in May's value this time.

 

I believe this is happening because once I filter for "IsCurrentMonth" it just narrows down the "Input Plant - Modified" table for June and nothing else exists.  That's why the formula can't find May's value any more.  Is there any way to pull May's value when filtering for "Current Month" in this case?


@Ashish_Mathur

Not clear about the whole picture, however, according to your description, you can try

Previous Month = 
IF(
 ISFILTERED('DimDate'[Calendar Date]),
 ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
 VAR __PREV_MONTH =
  CALCULATE(
   SUM('Input Plant - Modified'[Actual]),
   PARALLELPERIOD('DimDate'[Calendar Date].[Date], -1, MONTH),
	 ALL('Input Plant - Modified')
  )
 RETURN
  IF(ISBLANK(SUM('Input Plant - Modified'[Actual])), BLANK(),__PREV_MONTH  )
)

Capture.PNG

Anonymous
Not applicable

@Eric_Zhang, your solution worked as well.  Thank you for writing it up!

Hi @Eric_Zhang

 

That is what my solution would return with a smaller formula.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks for the solution Ashish.  Upon further inspection I noticed you have a select "Current Month" as a yes in your filters.  Further, I noticed that your PREVIOUSMONTH function used just the 'calendar'[DATE] as the field and whereas mine was using 'calendar'[DATE].[Date].  Or in other words, I was using Power BI's built in date heirarchy in my formula, while my "Current Month" filter was for just the column in my data source.  They weren't speaking to each other and I wasn't getting the results back.  Once I fixed this, I was able to get the results that I needed with your simplified formula.  Smiley Happy

You are welcone.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

Have you tried my solution?  If yes, then what problem are you facing?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.