Help on Creating a percentage measure from a current value against a previous value

[RachelS]

Hi everyone,

I've been trying for a while to create to create a percentage measure to use on a correlated matrix, but the percentage is just coming out wrong. I've trying creating a table storing population by dates and then divide that against a total population or using EARLIER as well but I know I'm doing something wrong here.

This is a very close example of fields and values I have, and the other screenshot is the final result I'm looking for to have.

Thank you very much in advanced! I'm very lost at this point.

RachelS

[RachelS]

@daxer-almighty 

Thank you very much! It worked wonderfully!

I really appreciate all your time and help on this matter

[RachelS]

@daxer-almighty 

thanks, I've attached the data sample.

The idea is to measure the percentage of people that have moved between Current Dates.

Initial Date 1-2021 population is 4

2 of these move to Current Date 2-2021, so it is 2/4 = 0,50 (2-2021 / 1-2021)

2 of the same 4 move to Current Date 3-2021, so it is 2/2 = 1 (3-2021 / 2-2021)

Initial Date 2-2021 population is 8

3 of these move to Current Date 3-2021, so it is 3/8 = 0,375 (3-2021 / 2-2021)

1 of the same 8 move to Current Date 4-2021, so it is 1/3 = 0,33 (4-2021 / 3-2021)

ID Initial Date InitialDateText Current Date CurrentDateText
ABC 1/04/2021 1-2021 1/04/2021 1-2021
ABC 1/04/2021 1-2021 1/05/2021 2-2021
ABC 1/04/2021 1-2021 1/06/2021 3-2021
ABC 1/04/2021 1-2021 1/07/2021 4-2021
BCD 1/04/2021 1-2021 1/04/2021 1-2021
BCD 1/04/2021 1-2021 1/05/2021 2-2021
CDE 1/04/2021 1-2021 1/04/2021 1-2021
CDE 1/04/2021 1-2021 1/06/2021 3-2021
DEF 1/04/2021 1-2021 1/04/2021 1-2021
CDE 1/04/2021 1-2021 1/07/2021 4-2021
123 1/05/2021 2-2021 1/05/2021 2-2021
123 1/05/2021 2-2021 1/06/2021 3-2021
123 1/05/2021 2-2021 1/07/2021 4-2021
147 1/05/2021 2-2021 1/05/2021 2-2021
345 1/05/2021 2-2021 1/05/2021 2-2021
345 1/05/2021 2-2021 1/06/2021 3-2021
567 1/05/2021 2-2021 1/05/2021 2-2021
765 1/05/2021 2-2021 1/05/2021 2-2021
789 1/05/2021 2-2021 1/05/2021 2-2021
789 1/05/2021 2-2021 1/06/2021 3-2021
876 1/05/2021 2-2021 1/05/2021 2-2021
951 1/05/2021 2-2021 1/05/2021 2-2021
A1B 1/06/2021 3-2021 1/06/2021 3-2021
A1B 1/06/2021 3-2021 1/07/2021 4-2021
B6D 1/06/2021 3-2021 1/06/2021 3-2021
H3D 1/06/2021 3-2021 1/06/2021 3-2021
H3D 1/06/2021 3-2021 1/07/2021 4-2021
J8S 1/06/2021 3-2021 1/06/2021 3-2021
L6U 1/06/2021 3-2021 1/06/2021 3-2021
Y5W 1/06/2021 3-2021 1/06/2021 3-2021
Y5W 1/06/2021 3-2021 1/07/2021 4-2021
BBB 1/07/2021 4-2021 1/07/2021 4-2021
CCC 1/07/2021 4-2021 1/07/2021 4-2021
DDD 1/07/2021 4-2021 1/07/2021 4-2021
EEE 1/07/2021 4-2021 1/07/2021 4-2021
FFF 1/07/2021 4-2021 1/07/2021 4-2021
GGG 1/07/2021 4-2021 1/07/2021 4-2021

I hope it helps and that I was able to make myself understand.

Many thanks!

[daxer-almighty · Accepted solution]

ID Count = DISTINCTCOUNT( Movements[ID] )

% Moved = 
IF( ISINSCOPE( Movements[CurrentDateText] ),
    var CurrentPeriod = SELECTEDVALUE( Movements[CurrentDateText] )
    var PreviousPeriod =
        TOPN(1,
            FILTER(
                ALLSELECTED( Movements[CurrentDateText] ),
                Movements[CurrentDateText] < CurrentPeriod
            ),
            Movements[CurrentDateText],
            DESC
        )
    var CountPreviously =
        CALCULATE(
            [ID Count],
            PreviousPeriod
        )
    var CountNow = [ID Count]
    return
        DIVIDE( CountNow, CountPreviously )
)

I called the table Movements, obviously...

[daxer-almighty]

@RachelS 

1. Could you please supply data in text format so that we can copy and paste into Excel/PBI?

2. Could you please explain the calculation logic behind the result?

Thanks.

[RachelS]

this would be the data sample

ID Initial Date InitialDateText Current Date CurrentDateText
ABC 1/04/2021 1-2021 1/04/2021 1-2021
ABC 1/04/2021 1-2021 1/05/2021 2-2021
ABC 1/04/2021 1-2021 1/06/2021 3-2021
ABC 1/04/2021 1-2021 1/07/2021 4-2021
BCD 1/04/2021 1-2021 1/04/2021 1-2021
BCD 1/04/2021 1-2021 1/05/2021 2-2021
CDE 1/04/2021 1-2021 1/04/2021 1-2021
CDE 1/04/2021 1-2021 1/06/2021 3-2021
DEF 1/04/2021 1-2021 1/04/2021 1-2021
CDE 1/04/2021 1-2021 1/07/2021 4-2021
123 1/05/2021 2-2021 1/05/2021 2-2021
123 1/05/2021 2-2021 1/06/2021 3-2021
123 1/05/2021 2-2021 1/07/2021 4-2021
147 1/05/2021 2-2021 1/05/2021 2-2021
345 1/05/2021 2-2021 1/05/2021 2-2021
345 1/05/2021 2-2021 1/06/2021 3-2021
567 1/05/2021 2-2021 1/05/2021 2-2021
765 1/05/2021 2-2021 1/05/2021 2-2021
789 1/05/2021 2-2021 1/05/2021 2-2021
789 1/05/2021 2-2021 1/06/2021 3-2021
876 1/05/2021 2-2021 1/05/2021 2-2021
951 1/05/2021 2-2021 1/05/2021 2-2021
A1B 1/06/2021 3-2021 1/06/2021 3-2021
A1B 1/06/2021 3-2021 1/07/2021 4-2021
B6D 1/06/2021 3-2021 1/06/2021 3-2021
H3D 1/06/2021 3-2021 1/06/2021 3-2021
H3D 1/06/2021 3-2021 1/07/2021 4-2021
J8S 1/06/2021 3-2021 1/06/2021 3-2021
L6U 1/06/2021 3-2021 1/06/2021 3-2021
Y5W 1/06/2021 3-2021 1/06/2021 3-2021
Y5W 1/06/2021 3-2021 1/07/2021 4-2021
BBB 1/07/2021 4-2021 1/07/2021 4-2021
CCC 1/07/2021 4-2021 1/07/2021 4-2021
DDD 1/07/2021 4-2021 1/07/2021 4-2021
EEE 1/07/2021 4-2021 1/07/2021 4-2021
FFF 1/07/2021 4-2021 1/07/2021 4-2021
GGG 1/07/2021 4-2021 1/07/2021 4-2021

The idea is to divide a Current Population Date by the previous one.

As in example:

From Initial Date 1-2021, the initial population is 4

2 of them are on Current Date 2-2021, so it is 2/4 = 0,50 (2-2021 / 1-2021)

2 of these 4 are also in Current Date 3-2021, so it is 2/2 = 1 (3-2021 / 2-2021)

From Initial Date 2-2021, the initial Population is 8

3 of them move to Current Date 3-2021, so it is 3/8 = 0,375 (3-2021 / 2-2021)

1 of these 8 is also in 4-2021, so it is 1/3 = 0,33 (4-2021 / 3-2021)

I hope I was able to make myself understand with the logic behind the measure.

Many thanks!