Forum Discussion
Aggregate Functions in Power BI
- Hi markarcane ,Because there are some limitations in the direct query connection mode: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:Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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
Hi there,
That solution should do the trick but here's a little example using your data.
