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

Helper II

## A cohort analysis (matrix visual)

Hi!

I have to be able to make a cohort analysis (in a matrix visual). In this analysis I have to sum up the quantity values by “year-month New” and go substracting this number to the total quantity of each month “year-month Out”.

For example, for the row 2023-06 (from “year-month New”)  I have a total sum of 199 and this is being substracted per month for the following month . This is the total result minus the quantity of 2023-07 (from “year-month Out”: 82+53) ==> 199 – 165 = 64

Table:

 "Product" "Quantity" "year-month New" "year-month Out" A 225 2023-05 2023-11 A 314 2023-05 2023-10 A 8 2023-05 2023-08 A 14 2023-05 2023-08 A 45 2023-05 2023-09 A 141 2023-05 2023-09 A 4 2023-05 2023-10 A 4 2023-05 2023-05 A 13 2023-05 2023-05 A 4 2023-05 2023-06 A 3 2023-05 2023-06 A 1 2023-05 2023-06 A 1 2023-05 2023-06 A 2 2023-05 2023-07 A 82 2023-06 2023-07 A 53 2023-06 2023-07 A 10 2023-06 2023-08 A 6 2023-06 2023-08 A 42 2023-06 2023-09 A 2 2023-06 2023-09 A 2 2023-06 2023-10 A 1 2023-06 2023-10 A 1 2023-06 2023-11

Total quantity (using year-month New):

 TOTAL 2023-05 779 2023-06 199

Result:

 1 2 3 4 5 6 7 8 9 10 11 2023-05 762 753 751 729 543 225 0 2023-06 199 64 48 4 1 0

1 ACCEPTED SOLUTION
Super User

@pg1980
See attached updated sample file

``````Measure =
VAR MonthDiff = MAX ( 'Months Difference'[Months] )
VAR T1 =
FILTER (
'Table',
DATEDIFF ( 'Table'[year-month New], 'Table'[year-month Out], MONTH )
>= MonthDiff - 1
)
VAR T2 =
FILTER (
T1,
DATEDIFF ( 'Table'[year-month New], 'Table'[year-month Out], MONTH )
>= MonthDiff
)
RETURN
DIVIDE (
SUMX ( T2, 'Table'[Quantity] ),
SUMX ( T1, 'Table'[Quantity] )
) + 0``````
6 REPLIES 6
Helper II

Hi @tamerj1! Thanks for your help!

It´s working!! And if I want to customize the numer of month using like this:

Months Difference= GENERATESERIES(1, 12, 1)
Super User

Yes you may do that. In this example 0 will be populated over all months greater than 7

Helper II

Hi @tamerj1,

I found a problem in the first measure you did, and also applies in the second measure.

If you don´t have a date in "year-month Out", its adding this amount in the first month.

For example:

 "Product" "Quantity" "year-month New" "year-month Out" A 2 2023-05 A 4 2023-05 A 3 2023-05 A 1 2023-05 A 2 2023-05

If you add this rows,  you have a total for "2023-05": 791

 1 2 3 2023-05 762 753 751

In the first month it has to be: 774 (791-(4+13)) , not 762 (791-(4+13+2+4+3+1+2))

This should be de right output:

 1 2 3 4 5 6 7 8 9 10 2023-05 774 765 763 741 555 237 12 12 12 12
Helper II

Hi @tamerj1 ,

it´s ok!

And if i have to divide like this:

 1 2 3 4 5 6 7 8 9 10 11 2023-05 (762/779) (753/762) (751/753) (729/751) (543/729) (225/543) (0/225) 2023-06 (199/199) (64/199) (48/64) (4/48) (1/4) (0/1)

In column n° 1: you have to divide the first value from the total --> 762 / 779= 0,97

In column n° 2: you have to divide the second value with the first value -> 753/762 = 0.98

in column n°3 : you have to divide the third value with the second value -> 751/753

in column n°4 : you have to divide the forth value with the thrd value ->  729/751

 1 2 3 4 5 6 7 8 9 10 11 2023-05 0,978 0,988 0,997 0,970 0,744 0,414 0 2023-06 1 0,321 0,75 0,08 0,25 0
Super User

@pg1980
See attached updated sample file

``````Measure =
VAR MonthDiff = MAX ( 'Months Difference'[Months] )
VAR T1 =
FILTER (
'Table',
DATEDIFF ( 'Table'[year-month New], 'Table'[year-month Out], MONTH )
>= MonthDiff - 1
)
VAR T2 =
FILTER (
T1,
DATEDIFF ( 'Table'[year-month New], 'Table'[year-month Out], MONTH )
>= MonthDiff
)
RETURN
DIVIDE (
SUMX ( T2, 'Table'[Quantity] ),
SUMX ( T1, 'Table'[Quantity] )
) + 0``````
Super User

Hi @pg1980
Please refer to attached sample filw with the proposed solution

``````Measure =
SUMX (
FILTER (
'Table',
DATEDIFF ( 'Table'[year-month New], 'Table'[year-month Out], MONTH )
>= MAX ( 'Months Difference'[Months] )
),
'Table'[Quantity]
) + 0``````

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.