Forum Discussion
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?
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]))
8 Replies
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]))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?
Hi,
Have you tried my solution? If yes, then what problem are you facing?