How to make PARALLELPERIOD function work with filters?

[cahghr]

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?

[Anonymous · Accepted solution]

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