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

Forum Discussion

Help