Forum Discussion
Create Customers and Date Table
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.
4 Replies
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 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 - _customer23. 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
tried it and it isnt grouped by Account but by Date only.. Expected output should be "by account by date per month"
