Aggregate Functions in Power BI

[markarcane]

Hi,

I'm new to Power BI.

I have a table of invoices, e.g.:-

InvRef | CustRef | InvDate

-------|---------|-----------

INV001 | CUST001 | 2019-03-05

INV002 | CUST002 | 2019-03-08

INV003 | CUST001 | 2019-03-15

INV004 | CUST002 | 2019-04-10

INV004 | CUST003 | 2019-04-11

I want to calculate how many accounts were opened each month, by finding the first invoice date for a given customer, then grouping and counting them by month.

I could do this in SQL with 2 queries:-

1. SELECT Min(InvDate) AS FirstInv FROM InvTable GROUP BY CustRef

2. SELECT Count(FirstInv) FROM InvQuery GROUP BY Month(FirstInv) & Year(FirstInv)

I am unsure how to approach the same thing in Power BI, can someone please point me in the right direction?

Thanks. 

[V-lianl-msft · Accepted solution]

Hi @markarcane ,

Because there are some limitations in the direct query connection mode:

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#implications-of-using-directquer... 

you could create a custom date hierarchy manually. Please create [Year], [Month], [Day] columns first, right-click the original Date column and choose 'New Hierarchy', then, drag [Year], [Month], [Day] columns to place them under [Date] column.

Then you can use the clustered column chart to summarize the invoice quantity of each customer every month.

You can also refer to my PBIX.

You can learn more through this website:

https://www.sqlbi.com/topics/from-sql-to-dax/ 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-lianl-msft]

Hi @markarcane ,

Because there are some limitations in the direct query connection mode.

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#implications-of-using-directquer... 

You could create a custom date hierarchy manually. Please create [Year], [Month], [Day] columns first, right-click the original Date column and choose 'New Hierarchy', then, drag [Year], [Month], [Day] columns to place them under [Date] column.

Then you can use the clustered column chart to summarize the invoice quantity of each customer every month.

You can also refer to the PBIX.

You can also learn more through this website:

https://www.sqlbi.com/topics/from-sql-to-dax/ 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[irobba]

You could add a Calculated column to your Invoices table, something like "Is New Account", which would just be a yes/no field. Then you can count the Yes' and filter by date as needs be.  

Is New Account :=
IF (
    CALCULATE (
        COUNTROWS ( 'Invoices' ),
        FILTER (
            ALLEXCEPT ( 'Invoices', 'Invoices'[CustRef] ),
            'Invoices'[InvDate] < EARLIER ( 'Invoices'[InvDate] )
        )
    ) > 0,
    "N",
    "Y"
)

New Accounts = CALCULATE(DISTINCTCOUNT('Invoices'[CustRef]), 'Invoices'[Is New Account]="Y")

[markarcane]

Thanks for this, I've tried to use it but every time I get an error in the EARLIER() function: Parameter is not the correct type. Cannot find name '[InvDate]'.

[irobba]

Hi Mark,

Sorry, is this an error cropping up in the sample file linked, or when you've tried to implement the methods in your own report? 

I've just linked it again here in case I'd put it up wrong the first time. 

Example PBIX 

If it's in your own report, would you be able to share the pbix so we can take a look at it? 

[Anonymous]

Create a Clustered column chart that uses a measure and a category. In this example, we're using Units Sold by Product. By default, Power BI creates a chart that sums the units sold (drag the measure into the Value well) for each product (drag the category into the Axis well).

In the Visualizations pane, right-click the measure, and select the aggregate type you need. In this case, we're selecting Average. If you don't see the aggregation you need, see the consideration and troubleshooting section.

Your visualization is now using aggregated by average.

I hope this information helps!

Regards,

Lewis

[cosminc]

Hi

in Query editor you can change column format in Date.

Hope it helps,

Cosmin

[Anonymous]

Hi

first of all: DAX is, unfortunately, hard to learn by copy paste from internet. You need a solid theoretical foundation to understand how it works. If you don't do it and simply copy a solution without understanding it will work, but as soon as you need something different you'll stumble in a situation where you try, numbers will appear, but will be wrong and you don't know it. So I suggest to learn at least the basics.

In this case your issue is a very basic thing you do in dax. To make it simple for you, mark the date as a date column.

Add a date slicer to the page and put in your date, then choose months.

Then add a table visual, put your custRef in the rows and InvRef in the values, it will sum the number of invoices grouped by custref and filtered by the month you select in the filter.

but again, the fact you can't do such a basic thing in pbi means that you're looking at the pbi desktop interface with no clue at all and trust me it's VERY dangerous, so i suggest to do some training first.

[markarcane]

Thanks for your response.

I take your point on making the effort to learn the language properly. I'm currently trying to satisfy myself that Power BI is the right tool for me before I press on with learning it in depth.

I think the solution you've suggested will display how many invoices each company has in a given month, that's not what I'm asking for.

I want a monthly count of first invoices for each company. Based on my supplied example data, the result would look like this:-

Period   | CountOfCustomerFirstInvoices
---------|-------------
Mar 2019 | 2
Apr 2019 | 1

[irobba]

Hi there, 

That solution should do the trick but here's a little example using your data.

shorturl.at/arH07