Forum Discussion
How to make PARALLELPERIOD function work with filters?
Hi All,
Need some urgent help with the following data.
| SLS_PERSON_CD | SLSMAN_NAME | OOS Event | VISIT_DT |
| 111001 | Richard | No | 15/Feb/16 |
| 111001 | Richard | Yes | 5/Feb/16 |
| 111001 | Richard | No | 1/Feb/16 |
| 111001 | Richard | No | 24/Jan/16 |
| 111001 | Richard | Yes | 24/Jan/16 |
| 111001 | Richard | No | 11/Jan/16 |
I need to display the solution in Power BI dashboard as
| SLS_PERSON_CD | SLSMAN_NAME | OOS Count (Current Month) | OOS Count (Previous Month) |
| 111001 | Richard | 1 | 1 |
I need a filter for the month for the dashboard so that if user selects "February", OOS Count should get displayed for January and February both.
The formulas used are:
OOS Count (Current Month) = TOTALMTD(CALCULATE(COUNTROWS('Distribution by Channel PC'), 'Distribution by Channel PC'[OOS Event] = "Yes"), 'Distribution by Channel PC'[Visit Date] )
OOS Count (Previous Month) = CALCULATE(COUNTROWS('Distribution by Channel PC'), 'Distribution by Channel PC'[OOS Event] = "Yes", PARALLELPERIOD('Distribution by Channel PC'[Visit Date], -1, MONTH))
ISSUE: The above formulas are giving me correct result but the constraint is in selection of filters. If I select "February" , OOS COUNT (Previous Month) is empty. If I multi-select the filter "January & February", both the columns give me correct result.
ASK: Can I display OOS COUNT (Previous Month) by having only "February" as my selection in the filter?
Hi cahghr,
Perhaps you can try to use below formulas to get the current month count and previous month count.
Calendar table:
Date = VALUES(Sheet1[VISIT_DT])
Measure to get selected value:
Select = IF(HASONEVALUE('Date'[VISIT_DT]),VALUES('Date'[VISIT_DT]),BLANK())CountEvent(Current) = CALCULATE(COUNT(Sheet1[OOS Event]),FILTER(ALLSELECTED(Sheet1),FORMAT([VISIT_DT],"mm/yyyy")=FORMAT([Select],"mm/yyyy")&&[OOS Event]="Yes")) CountEvent(Previous) = CALCULATE(COUNT(Sheet1[OOS Event]),FILTER(ALLSELECTED(Sheet1),FORMAT([VISIT_DT],"mm/yyyy")=FORMAT(DATE(YEAR([Select]),MONTH([Select]),1)-1,"mm/yyyy")&&[OOS Event]="Yes"))
Regards,
Xiaoxin Sheng
1 Reply
Hi cahghr,
Perhaps you can try to use below formulas to get the current month count and previous month count.
Calendar table:
Date = VALUES(Sheet1[VISIT_DT])
Measure to get selected value:
Select = IF(HASONEVALUE('Date'[VISIT_DT]),VALUES('Date'[VISIT_DT]),BLANK())CountEvent(Current) = CALCULATE(COUNT(Sheet1[OOS Event]),FILTER(ALLSELECTED(Sheet1),FORMAT([VISIT_DT],"mm/yyyy")=FORMAT([Select],"mm/yyyy")&&[OOS Event]="Yes")) CountEvent(Previous) = CALCULATE(COUNT(Sheet1[OOS Event]),FILTER(ALLSELECTED(Sheet1),FORMAT([VISIT_DT],"mm/yyyy")=FORMAT(DATE(YEAR([Select]),MONTH([Select]),1)-1,"mm/yyyy")&&[OOS Event]="Yes"))
Regards,
Xiaoxin Sheng