Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Conditional expression for PRODUCTX

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? 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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]))
      ```

       

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    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.