SAMEPERIODLASTYEAR() not working when categories are added to matrix

[RevOps608]

Hello,

I'm comparing Opportunities created MTD compared to same period last year. So August 23' to Aug 22'. My formula "MTD Opps" and MTD Opps (PY) both work when using dates as rows. But when I use categories(service lines), it breaks. On the second screen shot you'll see MTD OPPS filtered by Service Line. It's when I add MTD OPPS (PY) that it goes white and breaks. CreatedDate2__c and the opportunities being counted are located in the same table.

MTD Opps =

CALCULATE(

    COUNTROWS(Opportunity),

            DATESMTD(Opportunity[CreatedDate2__c]))

MTD Opps (PY) =

CALCULATE(

   [MTD Opps],

            SAMEPERIODLASTYEAR(Opportunity[CreatedDate2__c]))

[RevOps608]

Figure it out. It was the .[Date] ate the end of 

SAMEPERIODLASTYEAR(Opportunity[CreatedDate2__c].[Date])).

Power Bi makes me want to pull my hair out.

Thanks for all the help though! Much appreciated

[ToddChitt]

The chart on the right, the one that has Channel on the rows. does that have any knowledge of what MONTH you are talking about? If not it will fail.

Try this: Add a relative date slicer to the page

[RevOps608]

It's using CreatedDate2__c, which is part of the same table. So it should have no issue referencing, correct?

[mickey64]

How about measures below?

MTD Opps =
CALCULATE(
COUNTROWS(Opportunity),
DATESMTD(Calendar[Date]))

MTD Opps (PY) =
CALCULATE(
[MTD Opps],
SAMEPERIODLASTYEAR(Calendar[Date]))

[RevOps608]

Not what I expected.

I had to slightly alter your formula by adding USERELATIONSHIP. MTD Opps now gives me opps for 22' and MTD Opps (PY) doesnt work.

MTD Opps =

CALCULATE(

    COUNTROWS(Opportunity),

    USERELATIONSHIP('Calendar'[Date], Opportunity[CreatedDate2__c]),

            DATESMTD('Calendar'[Date]))

MTD Opps PY =

CALCULATE(

    [MTD Opps],

    USERELATIONSHIP('Calendar'[Date], Opportunity[CreatedDate2__c]),

    SAMEPERIODLASTYEAR('Calendar'[Date]])

    )))