Create a calculated row

[Anonymous]

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? 😞

[v-lili6-msft]

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]

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

[v-lili6-msft · Accepted solution]

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

[Mariusz]

Hi @Anonymous 

What would determine if the member?

Best Regards,
Mariusz

Please feel free to connect with me.

[Anonymous]

According to dataset;

Transaction Date= purchase date

Wallet Createddate= membership date

[Mariusz]

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.

[Anonymous]

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]

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

[Mariusz]

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.

[Anonymous]

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

[Mariusz]

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]

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.

[Anonymous]

I dont know how to add it, therefore I shared in link below.

https://we.tl/t-NsHcgshcNo