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 ,
Was wondering of you could help... i am very new to BI (as you will see) but thought i would ask.
I am trying to reflect the Count of the PersonID per month between Start and End Date.
The key being that it only counts the PeDistinct Person Code of the date shown on the Graph is within the Start and End Dates.
So if a Person Code is Between March and September 2017 - it will be counted once for each month.
TIA
Person Code | Start Date | End Date |
1 | 03/12/2018 | 15/02/2019 |
2 | 03/12/2018 | 15/02/2019 |
3 | 03/12/2018 | 15/02/2019 |
4 | 03/12/2018 | 15/02/2019 |
1 | 07/08/2017 | 01/12/2017 |
2 | 07/08/2017 | 01/12/2017 |
3 | 07/08/2017 | 01/12/2017 |
4 | 07/08/2017 | 23/03/2018 |
1 | 15/05/2017 | 15/11/2018 |
2 | 15/05/2017 | 19/06/2017 |
3 | 15/05/2017 | 15/11/2018 |
4 | 09/05/2017 | 10/05/2017 |
Solved! Go to Solution.
HI @Anonymous,
You can't direct use these date column to achieve your requirement, please take a look at following link to know how to create a detail data table to store expand date range and use it to direct calculate with records in date range.
Reference link:
Spread revenue across period based on start and end date, slice and dase this using different dates
Sample table formula:
Detail person records = VAR _calendar = CALENDAR ( MIN ( Table[Start Date] ), MAX ( Table[End Date] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Table, _calendar ), Table[Start Date] <= [Date] && Table[End Date] >= [Date] ), "Person Code", [Person Code], "Date", [Date] )
Notice: please don't forget to create relationship between new table and original table based on 'person code'.
Regards,
Xiaoxin Sheng
HI @Anonymous,
You can't direct use these date column to achieve your requirement, please take a look at following link to know how to create a detail data table to store expand date range and use it to direct calculate with records in date range.
Reference link:
Spread revenue across period based on start and end date, slice and dase this using different dates
Sample table formula:
Detail person records = VAR _calendar = CALENDAR ( MIN ( Table[Start Date] ), MAX ( Table[End Date] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Table, _calendar ), Table[Start Date] <= [Date] && Table[End Date] >= [Date] ), "Person Code", [Person Code], "Date", [Date] )
Notice: please don't forget to create relationship between new table and original table based on 'person code'.
Regards,
Xiaoxin Sheng
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |