Forum Discussion
Summarize and calculate difference using an index
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
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.
3 Replies
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.
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.
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.
