Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Compare Periods Only For Data Existing In Both Periods

I have a set of invoice data with unit costs for each invoice, and I built a report that uses two calendars to allow me to select two periods to compare the average unit cost to see how much of a pri...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    Please use the following measures

    Avg Unit Cost 2 = 
VAR vPartsInPeriod1 = VALUES(Invoices[Part Number])
VAR vPartsInPeriod2 = CALCULATETABLE(VALUES(Invoices[Part Number]), ALL('Calendar'), USERELATIONSHIP('Calendar'[DateKey], 'Period 2 Calendar'[DateKey]))
VAR vCommonParts = INTERSECT(vPartsInPeriod1,vPartsInPeriod2)
RETURN
CALCULATE([Avg Unit Cost],vCommonParts)
    Period 2 Avg Unit Cost 2 = 
VAR vPartsInPeriod1 = VALUES(Invoices[Part Number])
VAR vPartsInPeriod2 = CALCULATETABLE(VALUES(Invoices[Part Number]), ALL('Calendar'), USERELATIONSHIP('Calendar'[DateKey], 'Period 2 Calendar'[DateKey]))
VAR vCommonParts = INTERSECT(vPartsInPeriod1,vPartsInPeriod2)
RETURN
CALCULATE([Avg Unit Cost], ALL('Calendar'), USERELATIONSHIP('Calendar'[DateKey], 'Period 2 Calendar'[DateKey]),Invoices[Part Number] IN vCommonParts)
    Avg Invoice Unit Cost % Change = DIVIDE(([Avg Unit Cost 2] - [Period 2 Avg Unit Cost 2]), [Period 2 Avg Unit Cost 2])

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi Anonymous 

 

Please use the following measures

Avg Unit Cost 2 = 
VAR vPartsInPeriod1 = VALUES(Invoices[Part Number])
VAR vPartsInPeriod2 = CALCULATETABLE(VALUES(Invoices[Part Number]), ALL('Calendar'), USERELATIONSHIP('Calendar'[DateKey], 'Period 2 Calendar'[DateKey]))
VAR vCommonParts = INTERSECT(vPartsInPeriod1,vPartsInPeriod2)
RETURN
CALCULATE([Avg Unit Cost],vCommonParts)
Period 2 Avg Unit Cost 2 = 
VAR vPartsInPeriod1 = VALUES(Invoices[Part Number])
VAR vPartsInPeriod2 = CALCULATETABLE(VALUES(Invoices[Part Number]), ALL('Calendar'), USERELATIONSHIP('Calendar'[DateKey], 'Period 2 Calendar'[DateKey]))
VAR vCommonParts = INTERSECT(vPartsInPeriod1,vPartsInPeriod2)
RETURN
CALCULATE([Avg Unit Cost], ALL('Calendar'), USERELATIONSHIP('Calendar'[DateKey], 'Period 2 Calendar'[DateKey]),Invoices[Part Number] IN vCommonParts)
Avg Invoice Unit Cost % Change = DIVIDE(([Avg Unit Cost 2] - [Period 2 Avg Unit Cost 2]), [Period 2 Avg Unit Cost 2])

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This worked perfectly! Thank you!