Conditional expression for PRODUCTX

[Anonymous]

I had a measure which shows the number of remaining customers for each month, after counting for the predicted churn. 

CALCULATE(
    PRODUCTX('Year Month', 1-[Churn% Last Year]),
    FILTER(
        ALLSELECTED('Year Month'),
         'Year Month'[År Måned] <= MAX('Year Month'[År Måned])))
         *[Total customers at the start of year]

But now I want to change this measure so that it shows the actual value of customers if the month has passed. So for this year, it should show the actual number count for january, february and march, and then start multiplying march with Churn% Last Year to predict april etc.  

For this I have a Churn % This Year measure. 

I tried this: 

CALCULATE(
    PRODUCTX('Year Month', 1- IF('Year Month'[Before this month] = 1,
[Churn% This Year], [Churn% Last Year]),
    FILTER(
        ALLSELECTED('Year Month'),
         'Year Month'[År Måned] <= MAX('Year Month'[År Måned])))
         *[Total customers at the start of year]

But this one checks the if condition month by month, and for the future months, it ignores the results of Churn% This year.

I can't create a static table because I also have slicers, if activated, that replace Churn% Last year with the slicer value. 

Is it possible to create a dynamic conditional expression for the PRODUCTX like this? 

[v-xulin-mstf]

Hi @Anonymous

Could you provide sample data and expected output after removing sensitive data?

You can give examples of your expected output in excel format.
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Link

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

[amitchandak]

@Anonymous , can you share formula of

[Churn% This Year], [Churn% Last Year]

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

I have a `Churn% old users` column in the `Year Month` table. So the measures retrieve the info from that column 

```

Churn% This Year =

SELECTEDVALUE('Year Month'[Churn% old users])

```

```

Churn% Last Year = 

var _ret = CALCULATE(

MAX('Year Month'[Churn% old users]),

    FILTER(ALL('Year Month'),

        SELECTEDVALUE('Year Month'[År Måned LY]) = 'Year Month'[År Måned]))

//LY column is the previous year

return

    IF('Prosent Endring i Churn (gamle)'[Prosent Endring i Churn (gamle) Value] = 0,

        _ret,

            _ret*(1+'Prosent Endring i Churn (gamle)'[Prosent Endring i Churn (gamle) Value]))

```