Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I have to visualize some Social Services data and filter it by Service Year, however, this is a complicated expression and I have no idea how I would do this in DAX. Service Year is based on their program enrollments which can be continuous for multiple years, every year they are enrolled they are counted as served.
PersonID | Enrollment Date | Program | Exit Date |
1234 | 3/1/2010 | Program A | 8/21/2018 |
2345 | 6/23/2018 | Program B | |
3456 | 4/15/2017 | Program A | 1/13/2019 |
4567 | 9/16/2016 | Program A | |
5678 | 10/30/2013 | Program B |
PersonID 1234 enrolled in 3/1/2010 and exited the program 8/21/2018. They should be counted as a person served for 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, and 2018. Additionally, if their Exit Date is empty they should be counted as every year since Enrollment (not just the current year).
The logic is easy enough to write for one year but it won't get me ALL years that person was served. Would I write a conditional column for every year they were served and combine the data to get every year? Or is it possible to get all the values in one statement? Or do I create a reference table somehow? I have a date dimension already built out.
In fairness, I did post this already in the PowerBI User Group here but without any solutions.
Solved! Go to Solution.
Hi @Anonymous
Assuming your TableName is table1, try this calculated table
Calculated Table = GENERATE ( Table1, VAR Beg = YEAR ( Table1[Enrollment Date] ) VAR Fin = IF ( ISBLANK ( YEAR ( Table1[Exit Date] ) ), YEAR ( TODAY () ), YEAR ( Table1[Exit Date] ) ) RETURN SELECTCOLUMNS ( GENERATESERIES ( Beg, Fin ), "Service Year", [Value] ) )
@Anonymous
It will give you service years as a separate column for each person ID
Please see attached file as well
@Anonymous
It will give you service years as a separate column for each person ID
Please see attached file as well
Hi @Anonymous
Assuming your TableName is table1, try this calculated table
Calculated Table = GENERATE ( Table1, VAR Beg = YEAR ( Table1[Enrollment Date] ) VAR Fin = IF ( ISBLANK ( YEAR ( Table1[Exit Date] ) ), YEAR ( TODAY () ), YEAR ( Table1[Exit Date] ) ) RETURN SELECTCOLUMNS ( GENERATESERIES ( Beg, Fin ), "Service Year", [Value] ) )
@Zubair_Muhammad - this solution worked perfectly until I refreshed my data. Now I am getting an error that says "The arguments in GenerateSeries function cannot be blank." Do you know how to troubleshoot this? I have refresh data monthly.
@Zubair_Muhammad wrote:Hi @Anonymous
Assuming your TableName is table1, try this calculated table
Calculated Table = GENERATE ( Table1, VAR Beg = YEAR ( Table1[Enrollment Date] ) VAR Fin = IF ( ISBLANK ( YEAR ( Table1[Exit Date] ) ), YEAR ( TODAY () ), YEAR ( Table1[Exit Date] ) ) RETURN SELECTCOLUMNS ( GENERATESERIES ( Beg, Fin ), "Service Year", [Value] ) )
It means that one of either your enrollment date or exit dates are blank.
I ran into the same issue using this formula. I removed all null values and it worked fine.
User | Count |
---|---|
122 | |
109 | |
94 | |
59 | |
57 |
User | Count |
---|---|
143 | |
119 | |
102 | |
71 | |
61 |