cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Solution Sage

``````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...

5 REPLIES 5
Frequent Visitor

Thank you very much! It worked wonderfully!

I really appreciate all your time and help on this matter

Frequent Visitor

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!

Solution Sage

``````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...

Solution Sage

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.

Frequent Visitor

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.