Dynamic comparison among columns or rows with percentage

[BusinessAnalyst]

Hi everyone,

I have database as the below formats. 

ID	week 1	week 2	week 3	week 4
1	2	4	6	8
2	1	3	5	7
3	1	4	7	10
4	13	12	11	10

Or:

ID	week	Quantity
1	week 1	2
1	week 2	4
1	week 3	6
1	week 4	8
2	week 1	1
2	week 2	3
2	week 3	5
2	week 4	7
3	week 1	1
3	week 2	4
3	week 3	7
3	week 4	10
4	week 1	13
4	week 2	12
4	week 3	11
4	week 4	10

Both rows and columns will be increased, which means more ID and week number will be added by time.

I need to compare the growth or decrease of each ID over weeks, for example week 2 compares to week 1:

ID1:

- week 1 make 2

- week 2 make 4

So in week 2, ID1 make: (4 - 2)/2 * 100% = 100% -> The % growth = 100%

More complicated, compare 2 consecutive weeks, for example (week 3 + week 4) compare to (week 2 + week 1)

Any suggestion is highly appreciated!

Many thanks in advance!

Regards,

Cindy

[v-haibl-msft · Accepted solution]

@BusinessAnalyst

If you have more IDs and weeks, firstly add an index column as ankitpatira said. I’ve uploaded my .pbix file here for reference.

For the growth or decrease of each ID over week, we can create a column with following formula.

Growth_Decrease = 
VAR LastWeekIndex =
    IF (
        Table1[Index]
            = CALCULATE ( MIN ( Table1[Index] ), ALLEXCEPT ( Table1, Table1[ID] ) ),
        Table1[Index],
        Table1[Index] - 1
    )
VAR LastWeekQuantity =
    LOOKUPVALUE ( Table1[Quantity], Table1[Index], LastWeekIndex )
RETURN
    ( ( Table1[Quantity] - LastWeekQuantity )
        / LastWeekQuantity )

For the growth or decrease of 2 consecutive weeks, we can create some columns with following formulas.

Consecutive_Week = 
IF (
    DIVIDE ( Table1[Index], 2 ) > INT ( DIVIDE ( Table1[Index], 2 ) ),
    ( Table1[Index] + 1 )
        / 2,
    Table1[Index] / 2
)

Consecutive_WeekName =
"Consecutive Week "
    & RANKX (
        FILTER ( Table1, EARLIER ( Table1[ID] ) = Table1[ID] ),
        Table1[Consecutive_Week],
        ,
        ASC,
        DENSE
)

Growth_Decrease_2 = 
VAR LastWeekIndex =
    IF (
        Table1[Consecutive_Week]
            = CALCULATE ( MIN ( Table1[Consecutive_Week] ), ALLEXCEPT ( Table1, Table1[ID] ) ),
        Table1[Consecutive_Week],
        Table1[Consecutive_Week] - 1
    )
VAR LastWeekQuantity =
    CALCULATE (
        SUM ( Table1[Quantity] ),
        FILTER ( ALL ( Table1 ), Table1[Consecutive_Week] = LastWeekIndex )
    )
VAR ThisWeekQuantity =
    CALCULATE (
        SUM ( Table1[Quantity] ),
        ALLEXCEPT ( Table1, Table1[Consecutive_Week] )
    )
RETURN
    ( ( ThisWeekQuantity - LastWeekQuantity )
        / LastWeekQuantity )

Best Regards,

Herbert

[ankitpatira]

@BusinessAnalyst provided you have second screenshot data you can create calculated column using below DAX. Replace 'sam' with your table name. Before creating calculated column create Index column starting from 0 via query editor.

Column = sam[Quantity] - IF(
OR(sam[Index] = 0 , sam[Index] = 4),
sam[Quantity],
LOOKUPVALUE(
sam[Quantity],
'sam'[Index],
'sam'[Index]-1)
)