Drill down not working for quater

[Bikramaditya]

Hi All,

I have calaculated lost customeres revenue YOY, but when i drill down to quater it is not matching to the year as below image

here is the dax funtion i am using for Lost customer  YOY

Lost Rev YOY =

VAR lastYear = SELECTEDVALUE ( 'Calendar'[Date].[Year] ) - 1

VAR CustomersThisYear =  VALUES ( Sales[CUSTOMER_ID] )

RETURN

    CALCULATE ( SUM ( Sales[REVENUE] ),

        'Calendar'[Date].[Year] = lastYear,

        NOT Sales[CUSTOMER_ID] IN CustomersThisYear

    )

[Bikramaditya]

Noted,
Thank you for your response 

[daXtreme]

If the code is OK, please mark the answer as The Solution (there's a button for it). It'll help others as well. Thank you.

[Bikramaditya]

It did not work for me, attached the screenshot. Please have look

[daXtreme · Accepted solution]

Well, if you don't have the model as the code requires... then it'll not work. That's obvious. Also, it does matter which fields from which tables you drop onto the canvas. DAX does not work regardless of models. It does work together with models. That's why it's important that you inform potential helpers about what the model looks like...

[daXtreme]

// First of all, you should never use
// the auto-generated date tables. Never.
// There are many good reasons behind this
// and I don't have time to explain here.
// Please, just don't for your own safety
// and sanity.
//
// Once you've got the proper tables in place,
// use this code:

Lost Rev YOY =
    CALCULATE(
        // This SUM should be made into a basic
        // measure on its own, like [Total Revenue].
        SUM( Sales[REVENUE] ),
        // 'Calendar' should be marked as Date Table
        // in the model.
        DATEADD( 'Calendar'[Date], -1, YEAR),
        EXCEPT(
            // By the way, if you want to stay sane
            // please create a proper star schema
            // as the model and follow Best Practices
            // of data modeling in PBI. Otherwise,
            // you're exposing yourself to issues
            // you won't even be able to spot. So,
            // please create a Customer dimension and
            // use it instead of the field from the
            // fact table as below.
            ALL( Sales[Customer_id] ),
            VALUES( Sales[CUSTOMER_ID] )
        )
    )

You have to be extremely careful with what you're doing right now. The model is most likely not what it should be and therefore you might get funny results by slicing on fields that belong to the fact table. Honestly, slicing by a fact table's field should never be done in a well designed semantic solution. Please turn your model into a proper one to avoid issues.