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
Fabric Community Update - November 2024
Find out what's new and trending in the Fabric Community.
Be one of the first to start using Fabric Databases
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Power BI Monthly Update - November 2024
Check out the November 2024 Power BI update to learn about new features.
| User | Count |
|---|---|
| 92 | |
| 86 | |
| 84 | |
| 66 | |
| 49 |
