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: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

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
455 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
414 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
415 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
396 Views
0

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

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

View All