Need Help with DAX query

[Sim2312]

Hi Power BI Team,

I need help with the below query:

I have 2 tables - Accounts Table and Daily Balances table.

Accounts table holds the Customer No and the related Account no’s in them.
The Daily Balances table records the balance for each Account on a daily basis.
I have attached the sample dataset for your reference.
I need to find out the Customers who have funded thier Accounts in the first 10 days.

I came up with this , but I get 3 , whereas I should be getting 2, as only 101 and 103 funded thier accounts.

CALCULATE(COUNT(‘Accounts’[Customer No]),FILTER(‘Daily Balances’,‘Daily Balances’[Effective Date] >=TODAY()-11 && ‘Daily Balances’[Effective Date] <=TODAY()-1 ),‘Accounts’[Status]=“Active”)

Any help on this would be appreciated!

Thanks

[Paul]

Hi,

Could you share the pbix file pls?

Paul

[sam.mckay]

Just a quick thing, in future can you be more specific around your topic you add to the post.

For other details around this see below.

https://forum.enterprisedna.co/t/rules-and-tips-for-asking-support-questions-on-the-forum/30

A simple demo model with this setup would be appreciated here. This way we can run through a number of possibilities quickly.

It’s also not clear how you are visualizing this information or more important the ‘context’ of the calculation.

You should also be using a date table here, and not iterating through the fact table.

There’s actually a bit too this answer so to be able to work through a few things in a demo model is best I believe.

You likely also need to somehow in the formula iterate through every single customer and this isn’t being done currently with your formula.

Thanks
Sam

[Sim2312]

Hi,

Apologies , for not providing enough information on the problem I am working with.

Attaching the sample pbix file for your reference.

Report.pbix (118.9 KB)

[sam.mckay]

First, really look to format your measures. Trust me on this it will help a lot in your understanding of what is going on.

Simple indentation like this makes a big difference.

Also you need to set your models up correctly or it will constantly be confusing how formulas are operating.

This is very suboptimal and basically incorrect so nothing you did post this point was ever going to work out.

Go through this course as soon as you can.

Data Transformations & Modeling

This is how you want your model to be setup

These are the formulas I can up with

First Fund Date = 
CALCULATE( MIN( Balances[effective_dt] ),
    FILTER( Balances, Balances[Balance] > 0 ) )

Create Date = MIN( Accounts[Create Date] )

Funded Customers 10 Days = 
VAR RevisedBalances = FILTER( Balances, [Total Balance] > 0 )

RETURN
CALCULATE( DISTINCTCOUNT( Accounts[Customer No] ),
    FILTER( VALUES( Accounts[Customer No] ),
                [First Fund Date] <= [Create Date] + 10 &&
                [First Fund Date] >= [Create Date] ) )

Attached
Report demo.pbix (127.1 KB)

Thanks
Sam

[Sim2312]

Thank you so much, that worked perfectly!

[EnterpriseDNA]

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.