Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
shubh25
Helper I
Helper I

Can I transform this table from long data to comparative mode. Both Tables below.

‎09-02-2019 02:07 AM

I have data in the following form:

 

Date Balance
1-May-19120
2-May-19121
3-May-19122
4-May-19123
5-May-19124
6-May-19125
7-May-19126
8-May-19127
9-May-19128
10-May-19129
11-May-19130
12-May-19131
13-May-19132
14-May-19133
15-May-19134
16-May-19135
17-May-19136
18-May-19137
19-May-19138
20-May-19139
21-May-19140
22-May-19141
23-May-19142
24-May-19143
25-May-19144
26-May-19145
27-May-19146
28-May-19147
29-May-19148
30-May-19149
31-May-19150
1-Jun-19151
2-Jun-19152
3-Jun-19153
4-Jun-19154
5-Jun-19155
6-Jun-19156
7-Jun-19157
8-Jun-19158
9-Jun-19159
10-Jun-19160
11-Jun-19161
12-Jun-19162
13-Jun-19163
14-Jun-19164
15-Jun-19165
16-Jun-19166
17-Jun-19167
18-Jun-19168
19-Jun-19169
20-Jun-19170
21-Jun-19171
22-Jun-19172
23-Jun-19173
24-Jun-19174
25-Jun-19175
26-Jun-19176
27-Jun-19177
28-Jun-19178
29-Jun-19179
30-Jun-19180
1-Jul-19181
2-Jul-19182
3-Jul-19183
4-Jul-19184
5-Jul-19185
6-Jul-19186
7-Jul-19187
8-Jul-19188
9-Jul-19189
10-Jul-19190
11-Jul-19191
12-Jul-19192
13-Jul-19193
14-Jul-19194
15-Jul-19195
16-Jul-19196
17-Jul-19197
18-Jul-19198
19-Jul-19199
20-Jul-19200
21-Jul-19201
22-Jul-19202
23-Jul-19203
24-Jul-19204
25-Jul-19205
26-Jul-19206
27-Jul-19207
28-Jul-19208
29-Jul-19209
30-Jul-19210
31-Jul-19211
1-Aug-19212
2-Aug-19213
3-Aug-19214
4-Aug-19215
5-Aug-19216
6-Aug-19217
7-Aug-19218
8-Aug-19219
9-Aug-19220
10-Aug-19221
11-Aug-19222
12-Aug-19223
13-Aug-19224
14-Aug-19225
15-Aug-19226
16-Aug-19227
17-Aug-19228
18-Aug-19229
19-Aug-19230
20-Aug-19231
21-Aug-19232
22-Aug-19233
23-Aug-19234
24-Aug-19235
25-Aug-19236
26-Aug-19237
27-Aug-19238

 

I need this data in the following form: 

Date BalanceDate -1BalanceDate -2BalanceDate -3Balance
1-Aug-192121-Jul-191811-Jun-191511-May-19120
2-Aug-192132-Jul-191822-Jun-191522-May-19121
3-Aug-192143-Jul-191833-Jun-191533-May-19122
4-Aug-192154-Jul-191844-Jun-191544-May-19123
5-Aug-192165-Jul-191855-Jun-191555-May-19124
6-Aug-192176-Jul-191866-Jun-191566-May-19125
7-Aug-192187-Jul-191877-Jun-191577-May-19126
8-Aug-192198-Jul-191888-Jun-191588-May-19127
9-Aug-192209-Jul-191899-Jun-191599-May-19128
10-Aug-1922110-Jul-1919010-Jun-1916010-May-19129
11-Aug-1922211-Jul-1919111-Jun-1916111-May-19130
12-Aug-1922312-Jul-1919212-Jun-1916212-May-19131
13-Aug-1922413-Jul-1919313-Jun-1916313-May-19132
14-Aug-1922514-Jul-1919414-Jun-1916414-May-19133
15-Aug-1922615-Jul-1919515-Jun-1916515-May-19134
16-Aug-1922716-Jul-1919616-Jun-1916616-May-19135
17-Aug-1922817-Jul-1919717-Jun-1916717-May-19136
18-Aug-1922918-Jul-1919818-Jun-1916818-May-19137
19-Aug-1923019-Jul-1919919-Jun-1916919-May-19138
20-Aug-1923120-Jul-1920020-Jun-1917020-May-19139
21-Aug-1923221-Jul-1920121-Jun-1917121-May-19140
22-Aug-1923322-Jul-1920222-Jun-1917222-May-19141
23-Aug-1923423-Jul-1920323-Jun-1917323-May-19142
24-Aug-1923524-Jul-1920424-Jun-1917424-May-19143
25-Aug-1923625-Jul-1920525-Jun-1917525-May-19144
26-Aug-1923726-Jul-1920626-Jun-1917626-May-19145
27-Aug-1923827-Jul-1920727-Jun-1917727-May-19146
  28-Jul-1920828-Jun-1917828-May-19147
  29-Jul-1920929-Jun-1917929-May-19148
  30-Jul-1921030-Jun-1918030-May-19149
  31-Jul-19211  31-May-19150


I need to do this using DAX. Is there any way to do this?


Message 1 of 3
317 Views
0
1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

‎09-03-2019 12:03 AM

Hi @shubh25,

 

We can create such a table using such DAX, but it still need to modify if you have more than 4 months.

 

Comparative =
VAR maxDate =
    MAX ( Data[Date] )
VAR minDate =
    MIN ( Data[Date] )
VAR maxMonth =
    MONTH ( MAX ( Data[Date] ) )
VAR minMonth =
    MONTH ( MIN ( Data[Date] ) )
VAR t1 =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2019, maxMonth, 1 ), maxDate ),
        "Balance", LOOKUPVALUE ( Data[Balance], Data[Date], [Date] ),
        "DayKey", DAY ( [Date] )
    )
VAR d2 =
    DATE ( 2019, maxMonth - 1, 1 )
VAR t2 =
    ADDCOLUMNS (
        CALENDAR (
            d2,
            DATE ( 2019, MONTH ( d2 ), SWITCH (
                MONTH ( d2 ),
                1, 31,
                2, 28,
                3, 31,
                4, 30,
                5, 31,
                6, 30,
                7, 31,
                8, 31,
                9, 30,
                10, 31,
                11, 30,
                12, 31
            ) )
        ),
        "Balance2", LOOKUPVALUE ( Data[Balance], Data[Date], [Date] ),
        "DayKey", DAY ( [Date] )
    )
VAR t2r =
    SELECTCOLUMNS (
        t2,
        "Date-1", [Date],
        "Balance2", [Balance2],
        "DayKey", [DayKey]
    )
VAR d3 =
    DATE ( 2019, maxMonth - 2, 1 )
VAR t3 =
    ADDCOLUMNS (
        CALENDAR (
            d3,
            DATE ( 2019, MONTH ( d3 ), SWITCH (
                MONTH ( d3 ),
                1, 31,
                2, 28,
                3, 31,
                4, 30,
                5, 31,
                6, 30,
                7, 31,
                8, 31,
                9, 30,
                10, 31,
                11, 30,
                12, 31
            ) )
        ),
        "Balance3", LOOKUPVALUE ( Data[Balance], Data[Date], [Date] ),
        "DayKey", DAY ( [Date] )
    )
VAR t3r =
    SELECTCOLUMNS (
        t3,
        "Date-2", [Date],
        "Balance3", [Balance3],
        "DayKey", [DayKey]
    )
VAR d4 =
    DATE ( 2019, maxMonth - 3, 1 )
VAR t4 =
    ADDCOLUMNS (
        CALENDAR (
            d4,
            DATE ( 2019, MONTH ( d4 ), SWITCH (
                MONTH ( d4 ),
                1, 31,
                2, 28,
                3, 31,
                4, 30,
                5, 31,
                6, 30,
                7, 31,
                8, 31,
                9, 30,
                10, 31,
                11, 30,
                12, 31
            ) )
        ),
        "Balance4", LOOKUPVALUE ( Data[Balance], Data[Date], [Date] ),
        "DayKey", DAY ( [Date] )
    )
VAR t4r =
    SELECTCOLUMNS (
        t4,
        "Date-3", [Date],
        "Balance4", [Balance4],
        "DayKey", [DayKey]
    )
VAR result1 =
    NATURALLEFTOUTERJOIN ( t2r, t1 )
VAR result2 =
    NATURALLEFTOUTERJOIN ( result1, t3r )
VAR result3 =
    NATURALLEFTOUTERJOIN ( result2, t4r )
RETURN
    SELECTCOLUMNS (
        result3,
        "Date", [Date],
        "Balance", [Balance],
        "Date-1", [Date-1],
        "Balance-1", [Balance2],
        "Date-2", [Date-2],
        "Balance-2", [Balance3],
        "Date-3", [Date-3],
        "Balance-3", [Balance4]
    )

Can-I-transform-this-table-from-long-data-to-comparative-mode-1.png

 

BTW, pbix as attached.

 

Or we can use power query editor to do it , first copy the table into server same table depends on the number of month.

 

 

For each table, do the following steps

1. Add a column depends on the month

= Table.AddColumn(#"Changed Type", "Custom", each Date.MonthName([#"Date "]))

2. Pivot the table

= Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Custom]), "Custom", "Balance", List.Sum)

 

3. Using filter to select row for each month

= Table.SelectRows(#"Pivoted Column", each ([May] <> null))

4. Remove unnessary column

= Table.RemoveColumns(#"Filtered Rows",{"June", "July", "August"})

 

5. Add index

= Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)

 

6.Then use the nestjoin to each two tables

= Table.NestedJoin(#"Table (2)", {"Index"}, #"Table (3)", {"Index"}, "Table (3)", JoinKind.LeftOuter)

= Table.ExpandTableColumn(Source, "Table (3)", {"Date ", "June"}, {"Table (3).Date ", "Table (3).June"})

 

7. At last, remove the index column

= Table.RemoveColumns(#"Expanded Table (5)",{"Index"})

 

8. Finally, we can get the result as your requirement

 

Can-I-transform-this-table-from-long-data-to-comparative-mode-2.png

 

BTW, pbix as attached.

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 2 of 3
276 Views
2 REPLIES 2
v-lid-msft
Community Support
Community Support

‎09-03-2019 12:03 AM

Hi @shubh25,

 

We can create such a table using such DAX, but it still need to modify if you have more than 4 months.

 

Comparative =
VAR maxDate =
    MAX ( Data[Date] )
VAR minDate =
    MIN ( Data[Date] )
VAR maxMonth =
    MONTH ( MAX ( Data[Date] ) )
VAR minMonth =
    MONTH ( MIN ( Data[Date] ) )
VAR t1 =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2019, maxMonth, 1 ), maxDate ),
        "Balance", LOOKUPVALUE ( Data[Balance], Data[Date], [Date] ),
        "DayKey", DAY ( [Date] )
    )
VAR d2 =
    DATE ( 2019, maxMonth - 1, 1 )
VAR t2 =
    ADDCOLUMNS (
        CALENDAR (
            d2,
            DATE ( 2019, MONTH ( d2 ), SWITCH (
                MONTH ( d2 ),
                1, 31,
                2, 28,
                3, 31,
                4, 30,
                5, 31,
                6, 30,
                7, 31,
                8, 31,
                9, 30,
                10, 31,
                11, 30,
                12, 31
            ) )
        ),
        "Balance2", LOOKUPVALUE ( Data[Balance], Data[Date], [Date] ),
        "DayKey", DAY ( [Date] )
    )
VAR t2r =
    SELECTCOLUMNS (
        t2,
        "Date-1", [Date],
        "Balance2", [Balance2],
        "DayKey", [DayKey]
    )
VAR d3 =
    DATE ( 2019, maxMonth - 2, 1 )
VAR t3 =
    ADDCOLUMNS (
        CALENDAR (
            d3,
            DATE ( 2019, MONTH ( d3 ), SWITCH (
                MONTH ( d3 ),
                1, 31,
                2, 28,
                3, 31,
                4, 30,
                5, 31,
                6, 30,
                7, 31,
                8, 31,
                9, 30,
                10, 31,
                11, 30,
                12, 31
            ) )
        ),
        "Balance3", LOOKUPVALUE ( Data[Balance], Data[Date], [Date] ),
        "DayKey", DAY ( [Date] )
    )
VAR t3r =
    SELECTCOLUMNS (
        t3,
        "Date-2", [Date],
        "Balance3", [Balance3],
        "DayKey", [DayKey]
    )
VAR d4 =
    DATE ( 2019, maxMonth - 3, 1 )
VAR t4 =
    ADDCOLUMNS (
        CALENDAR (
            d4,
            DATE ( 2019, MONTH ( d4 ), SWITCH (
                MONTH ( d4 ),
                1, 31,
                2, 28,
                3, 31,
                4, 30,
                5, 31,
                6, 30,
                7, 31,
                8, 31,
                9, 30,
                10, 31,
                11, 30,
                12, 31
            ) )
        ),
        "Balance4", LOOKUPVALUE ( Data[Balance], Data[Date], [Date] ),
        "DayKey", DAY ( [Date] )
    )
VAR t4r =
    SELECTCOLUMNS (
        t4,
        "Date-3", [Date],
        "Balance4", [Balance4],
        "DayKey", [DayKey]
    )
VAR result1 =
    NATURALLEFTOUTERJOIN ( t2r, t1 )
VAR result2 =
    NATURALLEFTOUTERJOIN ( result1, t3r )
VAR result3 =
    NATURALLEFTOUTERJOIN ( result2, t4r )
RETURN
    SELECTCOLUMNS (
        result3,
        "Date", [Date],
        "Balance", [Balance],
        "Date-1", [Date-1],
        "Balance-1", [Balance2],
        "Date-2", [Date-2],
        "Balance-2", [Balance3],
        "Date-3", [Date-3],
        "Balance-3", [Balance4]
    )

Can-I-transform-this-table-from-long-data-to-comparative-mode-1.png

 

BTW, pbix as attached.

 

Or we can use power query editor to do it , first copy the table into server same table depends on the number of month.

 

 

For each table, do the following steps

1. Add a column depends on the month

= Table.AddColumn(#"Changed Type", "Custom", each Date.MonthName([#"Date "]))

2. Pivot the table

= Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Custom]), "Custom", "Balance", List.Sum)

 

3. Using filter to select row for each month

= Table.SelectRows(#"Pivoted Column", each ([May] <> null))

4. Remove unnessary column

= Table.RemoveColumns(#"Filtered Rows",{"June", "July", "August"})

 

5. Add index

= Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)

 

6.Then use the nestjoin to each two tables

= Table.NestedJoin(#"Table (2)", {"Index"}, #"Table (3)", {"Index"}, "Table (3)", JoinKind.LeftOuter)

= Table.ExpandTableColumn(Source, "Table (3)", {"Date ", "June"}, {"Table (3).Date ", "Table (3).June"})

 

7. At last, remove the index column

= Table.RemoveColumns(#"Expanded Table (5)",{"Index"})

 

8. Finally, we can get the result as your requirement

 

Can-I-transform-this-table-from-long-data-to-comparative-mode-2.png

 

BTW, pbix as attached.

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 2 of 3
277 Views
shubh25
Helper I
Helper I
In response to v-lid-msft

‎09-03-2019 03:05 AM

Hi @v-lid-msft ,
This worked a charm and also gave me many ideas for future exercises. I added a date column to make it a little dynamic. Also, that is a really creative way to use switch in calender function. 
Thank you for your effort.

Message 3 of 3
258 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All