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)

 

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

hi, Anonymous 

For your requirement, you could just use this simple way:

Create a measure by this formula:

Result = 
CALCULATE (
    COUNTA ( Sheet1[WalletID] ),
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[Wallet Createddate] >= MIN ( Sheet1[Transaction Date] )
            && Sheet1[Wallet Createddate] <= MAX ( Sheet1[Transaction Date] )
    )
)

And for your case, Wallet Createddate and Transaction Date are all DateTime column, you'd better create a date column for them in the calculation.

For example:

New Wallet Createddate = DATE(YEAR(Sheet1[Wallet Createddate]),MONTH(Sheet1[Wallet Createddate]),DAY(Sheet1[Wallet Createddate]))
New Transaction Date = DATE(YEAR(Sheet1[Transaction Date]),MONTH(Sheet1[Transaction Date]),DAY(Sheet1[Transaction Date]))

then just adjust the formula as below:

Result 2 = 
CALCULATE (
    COUNTA ( Sheet1[WalletID] ),
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[New Wallet Createddate] >= MIN ( Sheet1[New Transaction Date] )
            && Sheet1[New Wallet Createddate] <= MAX ( Sheet1[New Transaction Date] )
    )
)

Result:

and here is sample pbix file, please try it.

 

Regards,

Lin