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

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]
    )

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

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