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
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
Helpful resources
Power BI Monthly Update - November 2025
Check out the November 2025 Power BI update to learn about new features.
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
