Need a visualisation to show change over time - Sankey?

[grggmrtn]

Our data looks more or less like this:

Person	Service		Startdate	Stopdate
1	Support		01-01-2016	02-10-2018
2	Training	05-12-2017	12-12-2018
3	Housing		03-03-2005	07-08-2010
4	Vehicle		06-12-2015	31-12-9999
5	Meeting		12-05-2014	12-10-2015
1	Training	03-10-2018	01-01-2019
2	Housing		13-12-2018	29-01-2019
3	Housing		08-08-2010	05-02-2018

The same person in two different periods, can receive two different services, like person 1 and 2, or the person can continue with the same service in two different periods, such as person 3.
 
It wasn't a problem to create a simple line chart showing how many people received the different services over time (I've combined the start and end dates into a period using M). So - Axis = "date", legend = "Service", value = number of "Person". Boom.
 
But I'd also like to show movement. So - something like the what I have in my line chart, but which would ALSO show at the same time the number of "Person" who changed from the service "Support" to "Housing", or another Service, so we can follow trends/needs etc.
 
I was considering Sankey, but I'm kind of lost there. Any ideas?

[maxxmilo]

Hi @grggmrtn! Could you kindly post an example image of the sankey chart you ended up with? I'm interested in something similar. Thank you!

[grggmrtn · Accepted solution]

I actually found a work around myself. I don't know if it was the BEST solution, but like I said, it works 🙂

I started by creating a column in M that combined [Person] and [Startdate], meaning I would be able sort first by person, then by date.

    #"Insert combined column" = Table.AddColumn(#"Ændret type1", "Sorting column", each Text.Combine({[Person], Text.From([Startdate], "da-DK")}), type text),

Then I sorted that column, ascending, and removed it afterwards. Then I added an index column, starting with 0.

This gave me the possibility to use [Service] as my source column for Sankey, while using the index column to create a type of relative reference.

Then in DAX, I created the destination column:

Destination = 
IF (
    'Sankey'[Person]
        = LOOKUPVALUE (
            'Sankey'[Person];
            'Sankey'[Indeks]; 'Sankey'[Indeks] + 1
        );
    'Sankey'[Source];
    "Stopped"
)

This gave me a column that returned [Service] if [Person] in the index row below the current row was equal to [Person] in the current row. 

I don't know if I explained it very well, but it works. I'm happy. I can move on to the next steps 🙂

[grggmrtn]

This might be a bit tough to explain, but I'll do my best 🙂

Our data looks more or less like this:

Person	Service		Startdate	Stopdate
1	Support		01-01-2016	02-10-2018
2	Training	05-12-2017	12-12-2018
3	Housing		03-03-2005	07-08-2010
4	Vehicle		06-12-2015	31-12-9999
5	Meeting		12-05-2014	12-10-2015
1	Training	03-10-2018	01-01-2019
2	Housing		13-12-2018	29-01-2019
3	Housing		08-08-2010	05-02-2018

The same person in two different periods, can receive two different services, like person 1 and 2, or the person can continue with the same service in two different periods, such as person 3.

It wasn't a problem to create a simple line chart showing how many people received the different services over time (I've combined the start and end dates into a period using M). So - Axis = "date", legend = "Service", value = number of "Person". Boom.

But I'd also like to show movement. So - something like the what I have in my line chart, but which would ALSO show at the same time the number of "Person" who changed from the service "Support" to "Housing", or another Service, so we can follow trends/needs etc.

I was considering Sankey, but I'm kind of lost there. Any ideas?

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[grggmrtn]

Thanks for your help Greg, but it's not quite what I'm looking for. My problem isn't with the dates, it's with creating a source and destination table that will work in Sankey based on the dates.