Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

List Years between two dates or reference table

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.

PersonIDEnrollment DateProgramExit Date
12343/1/2010Program A8/21/2018
23456/23/2018Program B 
34564/15/2017Program A1/13/2019
45679/16/2016Program A 
567810/30/2013Program 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.

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

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] )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

It will give you service years as a separate column for each person ID

Please see attached file as well

 

listeyar.png


Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

It will give you service years as a separate column for each person ID

Please see attached file as well

 

listeyar.png


Regards
Zubair

Please try my custom visuals
Zubair_Muhammad
Community Champion
Community Champion

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] )
)

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@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] )
)

 

Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.