Is it possible to count in order by date?

[LV17]

Hello,

I am new to Power BI and  am wondering if there is a function in Power BI that can give the same result as regular Excel. I want to count the number of orders of a customer in order by date. I've added an example of my data from excel.

As you can see the total number of orders of a customer can be calculated in excel by the following function (this is for order on march 3th): =COUNT.IF(C:C;C8). And in Power BI I believe this can be achieved with the DISTINCTCOUNT function.

But for the nth order I use =COUNT.IF(C$2:C8;C8) in excel (this is for order on march 3th). So I get the value for the first, second, third, etc. order of a particular customer (because the column is sorted on the date). Now I am wondering if can achieve the same result in Power BI. And if so, how?

 

 

Thanks in advance!

Lars

[v-yueyunzh-msft · Accepted solution]

Hi , @LV17 

According to your description, you want to calcualte the [nth Order] and the [Total no. Order] column in Power BI . Right?

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can click "New Column" and enter the below Dax code to create two calculated columns:

nth Order = var _cur_CustomerID=[Customer ID]
var _curDate = [Date]
var _t = FILTER('Table', 'Table'[Date]<= _curDate && 'Table'[Customer ID] =_cur_CustomerID)
return
COUNTROWS(_t)

Total no. Orders = var _curCustomerID= [Customer ID] 
var _t = FILTER( 'Table','Table'[Customer ID]=_curCustomerID)
return
COUNTROWS(_t)

(3)Then we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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