Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a calculated row

I have a "User registration date" and "product purchase date" in my dataset...


Simply what I want to do is; when I change date range in slicer, how many of them have become members between these dates (new member)

 

I cannot create DAX or advanced queries since I have just started using PowerBI and I am not a developer :) Is there anyone who can help me to create measure I need? :(

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi, Anonymous 

    Use DISTINCTCOUNT in the formula

    Result 3 = var _fromdate=CALCULATE(MIN(Sheet1[New Transaction Date]),ALLSELECTED(Sheet1)) 
var _enddate=CALCULATE(MAX(Sheet1[New Transaction Date]),ALLSELECTED(Sheet1))  return
CALCULATE (
    DISTINCTCOUNT( Sheet1[WalletID] ),
    FILTER (
        Sheet1 ,
        Sheet1[New Wallet Createddate] >= _fromdate 
            && Sheet1[New Wallet Createddate] <= _enddate
    )
)

    Regards,

    Lin

13 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Can you create a data sample that represents your data model logic?

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski



  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

    What would determine if the member?

     

     

    Best Regards,
    Mariusz

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      According to dataset;

      Transaction Date= purchase date

      Wallet Createddate= membership date

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi Anonymous 

         

        I've attached the file with the solution.

        You will need to create a calendar table / date dimension with one active and one inactive relationship to achieve this.

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski

         

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi, Anonymous 

    Just adjust the formula as below:

    Result 3 = var _fromdate=CALCULATE(MIN(Sheet1[New Transaction Date]),ALLSELECTED(Sheet1)) 
var _enddate=CALCULATE(MAX(Sheet1[New Transaction Date]),ALLSELECTED(Sheet1))  return
CALCULATE (
    COUNTA ( Sheet1[WalletID] ),
    FILTER (
        Sheet1 ,
        Sheet1[New Wallet Createddate] >= _fromdate 
            && Sheet1[New Wallet Createddate] <= _enddate
    )
)

    Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Logic is correct, finally I got what I need. I need your help on fixing one thing. Result3 should calculate distinct.

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        6 years ago

        hi, Anonymous 

        Use DISTINCTCOUNT in the formula

        Result 3 = var _fromdate=CALCULATE(MIN(Sheet1[New Transaction Date]),ALLSELECTED(Sheet1)) 
var _enddate=CALCULATE(MAX(Sheet1[New Transaction Date]),ALLSELECTED(Sheet1))  return
CALCULATE (
    DISTINCTCOUNT( Sheet1[WalletID] ),
    FILTER (
        Sheet1 ,
        Sheet1[New Wallet Createddate] >= _fromdate 
            && Sheet1[New Wallet Createddate] <= _enddate
    )
)

        Regards,

        Lin