Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
I have attached format database of power sector, Where master table have the records of user activation date/Billinfo has the records on monthly basis bill generation/ same way payment has the records on monthly basis payment details.
Once consumer gets activated, we generate bill for user on every month & user also may or may not pay every month(As user can 02 month bill on at once as well)
Now as per the attach tables, i want to get the details for every month, for example nov month i need below,
Total users - 9
Biiled user - 8
Paid user - 7
how to acheive this.
Thanks,
Ashraf
Solved! Go to Solution.
You can create date dimension and join all three dates with that. One active other inactive. Using userelation, you can create measures that can tell based on each date active, billed and paied.
check how to use multiple date at :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Hi,
You can try to use SelectColumns function to create three tables which have the same number of columns.
And then you can union them and follow the steps in my previous reply.
Best Regards,
Giotto Zhi
Hi,
Please try these measures:
Total Users = COUNTROWS(DISTINCT(Master[UserNo]))
COUNTROWS(BillInfo)
User Paid = COUNTROWS(Payment)
And it shows:
Best Regards,
Giotto Zhi
@v-gizhi-msft ..if i filter this by month, It gives me total user For Sep -1, Oct - 7 & Nov -1
but in actual it should be Sep -1, Oct - 8 & Nov -9 (need total no of user till date for master table & for bill & paid only distinct count of that month)
Hi,
Please try to create a calculated table:
Table = UNION(BillInfo,Payment,Master)
Then change the original total measure to this:
Total Users = CALCULATE(DISTINCTCOUNT('Table'[UserNo]),ALLEXCEPT('Table','Table'[Bill Date].[Month]))
And the result shows:
Best Regards,
Giotto Zhi
@v-gizhi-msft For union all tables must have same no of columns which i don't have in my database
Hi,
You can try to use SelectColumns function to create three tables which have the same number of columns.
And then you can union them and follow the steps in my previous reply.
Best Regards,
Giotto Zhi
Master | BillInfo | Payment | |||||
UserNo | Active date | UserNo | Bill Date | UserNo | Payment Date | ||
1100 | 1-Sep-19 | 1100 | 15-Oct-19 | 1100 | 25-Oct-19 | ||
1101 | 1-Oct-19 | 1101 | 16-Oct-19 | 1101 | 26-Oct-19 | ||
1102 | 2-Oct-19 | 1102 | 15-Oct-19 | 1102 | 25-Oct-19 | ||
1103 | 3-Oct-19 | 1103 | 18-Oct-19 | 1103 | 26-Oct-19 | ||
1104 | 4-Oct-19 | 1104 | 15-Oct-19 | 1104 | 25-Oct-19 | ||
1105 | 5-Oct-19 | 1105 | 14-Oct-19 | 1105 | 27-Oct-19 | ||
1106 | 6-Oct-19 | 1106 | 18-Oct-19 | 1100 | 25-Nov-19 | ||
1107 | 7-Oct-19 | 1100 | 15-Nov-19 | 1101 | 26-Nov-19 | ||
1108 | 7-Nov-19 | 1101 | 16-Nov-19 | 1102 | 25-Nov-19 | ||
Result | 1102 | 17-Nov-19 | 1103 | 27-Nov-19 | |||
Total Users | 9 | 1103 | 18-Nov-19 | 1104 | 28-Nov-19 | ||
1104 | 19-Nov-19 | 1105 | 25-Nov-19 | ||||
Month | Users Billed | 1105 | 20-Nov-19 | 1106 | 25-Nov-19 | ||
Oct | 7 | 1106 | 17-Nov-19 | 1100 | 25-Dec-19 | ||
Nov | 8 | 1107 | 18-Nov-19 | 1101 | 26-Dec-19 | ||
Dec | 9 | 1100 | 15-Dec-19 | 1102 | 25-Dec-19 | ||
1101 | 16-Dec-19 | 1103 | 26-Dec-19 | ||||
Month | Users Billed | 1102 | 15-Dec-19 | 1104 | 25-Dec-19 | ||
Oct | 6 | 1103 | 18-Dec-19 | 1105 | 27-Dec-19 | ||
Nov | 7 | 1104 | 15-Dec-19 | 1106 | 25-Dec-19 | ||
Dec | 9 | 1105 | 14-Dec-19 | 1107 | 26-Dec-19 | ||
1106 | 15-Dec-19 | 1108 | 25-Dec-19 | ||||
1107 | 18-Dec-19 | ||||||
1108 | 14-Dec-19 |
You can create date dimension and join all three dates with that. One active other inactive. Using userelation, you can create measures that can tell based on each date active, billed and paied.
check how to use multiple date at :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |