Summarize and calculate difference using an index

[NasTr]

Hi all, As you understand I am a newbie in dax and power query!

Before posting I have searched and tryied lots of things but wasn't able to find the solution.

I have a table that has lots of columns ( var1, var2, cyindex, lyindex,month etc) which contain not unique values.

I am trying to summarize 'TTL' by CYindex and by var1 and then calculate the difference where LYindex is CYIndex, Something like the defference from previous that we have in pivots, only now with continuous data, no dates and previous being defined by matching index... Any help?

sample data:

Var1	CY index	LY Index	month	Var2	TTL
a	201911	201910	11	A	18
a	201911	201910	11	G	10
a	201911	201910	11	I	76
a	201912	201911	12	A	15
a	201912	201911	12	I	76
a	202002	202001	2	I	36
b	201910	201909	10	I	397
b	201911	201910	11	A	77
b	201911	201910	11	I	873
b	201912	201911	12	A	262
b	201912	201911	12	G	398
b	201912	201911	12	I	1940
b	202001	201912	1	I	64
b	202002	202001	2	I	348
b	202003	202002	3	A	295
b	202003	202002	3	G	226
b	202003	202002	3	I	430
c	201910	201909	10	A	27
c	201910	201909	10	I	378
c	201911	201910	11	A	313
c	201911	201910	11	G	2556
c	201911	201910	11	I	1885
c	201912	201911	12	A	342
c	201912	201911	12	G	1413
c	201912	201911	12	I	1914
c	202001	201912	1	A	96
c	202001	201912	1	I	242
c	202002	202001	2	A	110
c	202002	202001	2	G	17
c	202002	202001	2	I	454
c	202003	202002	3	A	257
c	202003	202002	3	I	307
d	201910	201909	10	I	21
d	201911	201910	11	A	99
d	201911	201910	11	I	129
d	201912	201911	12	A	48
d	201912	201911	12	G	239
d	201912	201911	12	I	122
d	202001	201912	1	A	15
d	202001	201912	1	I	51
d	202002	202001	2	I	260
d	202003	202002	3	A	29
d	202003	202002	3	G	246
d	202003	202002	3	I	432

desired result 1: 

Var1	CY index	LY Index	Sum of TTL	LY ttl
b	201910	201909	397
b	201911	201910	950	397
b	201912	201911	2600	950
b	202001	201912	64	2600
b	202002	202001	348	64
b	202003	202002	951	348

desired result 2:

var1	Sum of TTL	Sum of LY ttl
b	5310	4359

Thank you

[Icey · Accepted solution]

Hi @NasTr ,

Try this:

1. Create a calculated column.

When the data type of [CY index] column is number, try this:

CY Date = DATE ( LEFT ( [CY index], 4 ), RIGHT ( [CY index], 2 ), 1 )

When the data type of [CY index] column is text, try this:

CY Date = DATE ( CONVERT ( LEFT ( [CY index], 4 ), INTEGER ), CONVERT ( RIGHT ( [CY index], 2 ), INTEGER ), 1 )

2. Create a measure.

LY TTL =
VAR t =
    ADDCOLUMNS (
        'Table',
        "LY_TTL",
            CALCULATE (
                SUM ( 'Table'[TTL] ),
                ALLEXCEPT ( 'Table', 'Table'[Var1] ),
                PREVIOUSMONTH ( 'Table'[CY Date] )
            )
    )
VAR t2 =
    SUMMARIZE ( t, [Var1], [CY index], [LY_TTL] )
RETURN
    SUMX ( t2, [LY_TTL] )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NasTr]

Hi, @CNENFRNL thank you for your quick response. 

Unfortunately that didn't do the trick as date and var1 is not unique, but more important date is not continuous. So I might have var1 for 01/08/2019 but since I am missing 01/07/2019 it returns wrong value. 

Can I use something like max of list of earlier dates...and of course how! 

Also apart from var1 I have also other variables that I need the measure to react the same.

[CNENFRNL]

Hi, @NasTr , DAX does the trick without sweat, much easier than PQ, I think.

You might want to refer to the attached file for details.