PARALLELPERIOD Filter Problem

---

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  )
)