Forum Discussion

cahghr's avatar
cahghr
New Member
9 years ago
Solved

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 Richa...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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