Create Customers and Date Table

[JMAL79]

Hi Experts, 

I am new to PowerBI and I have an excel file as my source and raw data has a table as follows (customer, issue key, date opened, date closed). Is there a way to create a customer table looking like the picture below? Would need to calculate Inflow/Outflow by customer by Month. 

[v-yangliu-msft]

Hi  @JMAL79 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 = DISTINCT('Table'[Create date])

2. Create measure.

Inflow =
    COUNTX(FILTER(ALL('Table'),'Table'[Create date]<=MAX('Table 2'[month])&&'Table'[Close date]>MAX('Table 2'[month])),[Account])

Outflow =
var _customer1=
COUNTX(FILTER(ALL('Table'),'Table'[Close date]<=MAX('Table 2'[month])),[Account])
var _customer2=
COUNTX(FILTER(ALL('Table'),'Table'[Close date]<=MAX('Table 2'[month])-1),[Account])
return
_customer1 - _customer2

3. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

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

[JMAL79]

tried it and it isnt grouped by Account but by Date only.. Expected output should be "by account by date per month"

[JMAL79]

Hi @PijushRoy thanks for your response. Raw Data looks something like this. Would want to get a table summarizing inflow and outflow "per customer per month in year". Somwhat like when you pivot table in excel.

Case Number	Customer Name	Date Opened	Date Closed
1	Customer 1	10/22/2018	11/12/2018
2	Customer 1	6/6/2018	6/27/2018
3	Customer 2	11/2/2018	11/23/2018
4	Customer 2	11/5/2018	11/26/2018
5	Customer 2	11/5/2018	11/26/2018
6	Customer 3	6/4/2018	6/25/2018
7	Customer 3	11/6/2018	11/27/2018
8	Customer 3	11/6/2018	11/27/2018
9	Customer 3	11/8/2018	11/29/2018

[PijushRoy]

Hi @JMAL79 

Please share sample data (not sensetive data)