Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Lovely People 🙂
I want to calculate ATTRITION- - Only have, 'Report Date' (no start date or end date) and 'Name' (ignoring the other columns)
Name | Report date |
a | 01/01/2016 |
b | 01/01/2016 |
c | 01/01/2016 |
d | 02/01/2016 |
a | 01/01/2017 |
b | 01/01/2017 |
c | 01/01/2017 |
a | 01/01/2018 |
c | 01/01/2018 |
a | 01/01/2019 |
---
I want to see - give me all the attrition number for year 2016/17/18/19 etc
| 2016 | 2017 | 2018 | 2019 |
total | 0 | 1 | 2 | 1 |
---Logic Approach
Visual Diagram
Power BI Matrix view
Name | 2016 | 2017 | 2018 | 2019 |
A | 1 | 1 | 1 | 1 |
B | 1 | 1 |
|
|
C | 1 | 1 | 1 |
|
d | 1 | 1 |
|
|
|
|
|
|
|
To get the above – Power matric Name-row, report date
Report Date – Column
Value – ‘my Measure’
‘my Measure’ = CALCULATE(COUNT(Sheet1[Name]),FILTERS(Sheet1[Name]),DATESBETWEEN(datetable[Date].[Date],MIN(Sheet1[Report Date]),MAX(Sheet1[Report Date])))
***ERROR
So I checked my measure on Matrix view
‘my Measure’ = CALCULATE(COUNT(Sheet1[Name]),FILTERS(Sheet1[Name]),DATESBETWEEN(datetable[Date].[Date],MIN(Sheet1[Report Date]),MAX(Sheet1[Report Date])))
What it does where name is not in One of the years - it adds Value 1
for example
total Year attrition | ||||
Name | 2016 | 2017 | 2018 | 2019 |
A | 1 | 1 | 1 | 1 |
B | 1 | 1 | 1 | 1 |
C | 1 | 1 | 1 | 1 |
d | 1 | 1 | 1 | 1 |
But when I did individual year in measure
Hi @Anonymous ,
I still have a little confused about your logic.
If it is convenient, could you share your desired output and describe your logic in more details so that we could help further on it.
Best Regards,
Cherry
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |