New Measure with filter by Date measure

[Anonymous]

Hi all,

I am a little bit stuck. Maybe its too easy for me to see. 

Following situation.

I created a "date of first sale" measure for my products.

Now i want to create a new measure which gives me the sales of all products that have a "date of first sale" less than 12 months ago.

Basically i am trying to develop a marketing KPI on how much % products which had their "date of first sale" in the last 12 months contributed to my total sales.

My idea was:

Inovoiced Sales - New products = CALCULATE(SalesInvoiceV2Lines[Invoiced sales];FILTER(SalesInvoiceV2Lines;[Date of first sale] >= (DATEADD(DateDimension[FullDateAlternateKey].[Date];-12;MONTH))))

I appreciate any input.

Thanks and BR,

Paul

[v-yiruan-msft]

Hi @Anonymous ,

Please try to create a measure as below:

Marketing KPI =
DIVIDE (
    CALCULATE (
        SUM ( SalesInvoiceV2Lines[Invoiced sales] ),
        SAMEPERIODLASTYEAR ( DateDimension[FullDateAlternateKey].[Date] )
    ),
    SUMX ( ALL ( SalesInvoiceV2Lines ), SalesInvoiceV2Lines[Invoiced sales] )
)

If the above measure is not applicable for your scenario, please provide some sample data in table SalesInvoiceV2Lines(exclude sensitive data) and the formula of your measure "date of first sale". Also please describe your expected result with screen shot. Thank you.

Best Regards

Rena

[Anonymous · Accepted solution]

Hi,

I figured it out.

For me works. Dont know though if my approach is smooth though. 

Invoiced sales new products = CALCULATE(SalesInvoiceV2Lines[Invoiced sales]; DATESINPERIOD(ReleasedProductsV2[Date of first sale].[Date];DATE(YEAR(TODAY());MONTH(TODAY()); DAY(TODAY()));-1;YEAR))

Thanks and BR,

Paul