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