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 d...
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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you so much for answer but I think I couldnt explain myself clearly. Therefore, your calculation doesnt give me what I need.

 

***Slicer should be based on sales date, "transaction date" in dataset. But you setted it as "Wallet Createddate"

 

In short, what I want to calculate is; sales qty of new members within the specified date range

Example;

If I choose the dates between 2019-09-18 and 2019-10-12(***),

It should check the "Wallet Created Date" column and verify the dates are same as selected in slicer and then calculate as "5" (example below)

 

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Sorry but I'm struggling to understand, how 2019-09-10 could be considered within 2019-10-10 to 2019-10-12 range?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Mariusz , Im so sorry, I updated my previous post. Please kindly check it again.

    • Mariusz's avatar
      Mariusz
      Community Champion
      6 years ago

      Hi Anonymous 

       

      Please see the below and the attached file.

      The only change I have made is adjusted WalltID 878982 Wallet Createddate date back to 02/09/2019.

       

      according to your recent post, the result should be correct. 

       
      Best Regards,
      Mariusz

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

      Please feel free to connect with me.
      Mariusz Repczynski