Need help with a measure and conditional statements for how to calculate it

[AnthFromIngram]

Need to build a custom measure to say count or sum the total Sales[Net Sales] x 12 unless subscription[plan name] contains text (NCE COM ANN) if contains text (NCE COM ANN) then count it but do not multiply it's value by 12.

Essentially the issue im trying to solve is, i'm trying to get the total annual revenue from the column Sales[Net Sales] but I only want to multiply the monthly subscriptions by 12. 

I'm not sure what the correct syntax for this would be, and its tough to find any info to help piece this together myself.

There's also a slicer on the page for relative date, I would need the net sales total to reflect the total for the selected timeframe. 

I'm working with a direct query so creating columns or tables isn't an option as it'll turn it into a composite model and won't allow for scheduled refresh anymore. Has to be a measure.

Anything helps! Thank you!

[AnthFromIngram]

Thank you so much! this worked great, I didnt need to setup a date table because there was already one in the datamodel so the date slicer i had on each page works fine.

[v-rzhou-msft · Accepted solution]

Hi @AnthFromIngram ,

If you want to show results group by month, I suggest you to create a dimdate and then create a relationship between it with your data table.

DimDate =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [date] ),
    "Month", MONTH ( [date] )
)

Measure:

Filter =
VAR _Net_Sales1 =
    CALCULATE (
        SUM ( Sales[Net Sales] ),
        FILTER ( subscription, subscription[plan name] <> "NCE COM ANN" )
    )
VAR _Net_Sales2 =
    CALCULATE (
        SUM ( Sales[Net Sales] ),
        FILTER ( subscription, subscription[plan name] = "NCE COM ANN" )
    )
RETURN
    _Net_Sales1 * 12 + _Net_Sales2

If this reply still couldn't help you solve your issue, please share a sample file with me and show me the result you want.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.