Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, everyone,
Recently I was assigned a task to create a table looking like the screenshot below.
And the source data looks like this:
This could be easily achieved with the table visual. However, my boss wants to see a rolling 12 months table. From the next month, the first column would be "Prior to Aug", and the second column would be "Aug".
According to the link below, I might be able to do it in a matrix. However, the "Total" Column does not equal to the column total, it is the total number of orders later or after July.
https://community.powerbi.com/t5/Desktop/Dynamic-Column-Header-Names-based-month/td-p/1847178
In addition, I don't know how to put the percentage field onto the matrix, which equals to the number of orders later or after July divided by the number of all orders.
@amitchandak , @SpartaBI , @Ashish_Mathur , @DataInsights , @MFelix
Solved! Go to Solution.
Hi @Jacob_Li ,
According to your description, here's my solution.
1.Create a new table.
Table =
UNION (
ROW (
"Month",
"Prior to "
& FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 2, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 3, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 4, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 5, 1 ), "MMM" )
),
ROW ( "Month", "Total" ),
ROW ( "Month", "Order%" )
)
In order to let the Month column sort as expected, create a calculated column in the new table.
Column =
SWITCH (
'Table'[Month],
"Prior to "
& FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" ), 1,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" ), 2,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 2, 1 ), "MMM" ), 3,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 3, 1 ), "MMM" ), 4,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 4, 1 ), "MMM" ), 5,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 5, 1 ), "MMM" ), 6,
"Total", 7,
"Order%", 8
)
Result:
2.Create a measure.
Value =
SWITCH (
MAX ( 'Table'[Column] ),
1,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] ) <= MONTH ( TODAY () )
)
) + 0,
2,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 1
)
) + 0,
3,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 2
)
) + 0,
4,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 3
)
) + 0,
5,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 4
)
) + 0,
6,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 5
)
) + 0,
7,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] ) > MONTH ( TODAY () )
&& MONTH ( 'Table (2)'[Order Date] )
< MONTH ( TODAY () ) + 6
)
) + 0,
8,
FORMAT (
DIVIDE (
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] ) > MONTH ( TODAY () )
&& MONTH ( 'Table (2)'[Order Date] )
< MONTH ( TODAY () ) + 6
)
),
COUNTROWS (
FILTER ( 'Table (2)', 'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] ) )
)
),
"Percent"
)
)
Put Customer in Rows, rank column and Month in Columns, Value measure in Values, get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Jacob_Li ,
According to your description, here's my solution.
1.Create a new table.
Table =
UNION (
ROW (
"Month",
"Prior to "
& FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 2, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 3, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 4, 1 ), "MMM" )
),
ROW (
"Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 5, 1 ), "MMM" )
),
ROW ( "Month", "Total" ),
ROW ( "Month", "Order%" )
)
In order to let the Month column sort as expected, create a calculated column in the new table.
Column =
SWITCH (
'Table'[Month],
"Prior to "
& FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" ), 1,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" ), 2,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 2, 1 ), "MMM" ), 3,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 3, 1 ), "MMM" ), 4,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 4, 1 ), "MMM" ), 5,
FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 5, 1 ), "MMM" ), 6,
"Total", 7,
"Order%", 8
)
Result:
2.Create a measure.
Value =
SWITCH (
MAX ( 'Table'[Column] ),
1,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] ) <= MONTH ( TODAY () )
)
) + 0,
2,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 1
)
) + 0,
3,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 2
)
) + 0,
4,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 3
)
) + 0,
5,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 4
)
) + 0,
6,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] )
= MONTH ( TODAY () ) + 5
)
) + 0,
7,
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] ) > MONTH ( TODAY () )
&& MONTH ( 'Table (2)'[Order Date] )
< MONTH ( TODAY () ) + 6
)
) + 0,
8,
FORMAT (
DIVIDE (
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
&& MONTH ( 'Table (2)'[Order Date] ) > MONTH ( TODAY () )
&& MONTH ( 'Table (2)'[Order Date] )
< MONTH ( TODAY () ) + 6
)
),
COUNTROWS (
FILTER ( 'Table (2)', 'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] ) )
)
),
"Percent"
)
)
Put Customer in Rows, rank column and Month in Columns, Value measure in Values, get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Jacob_Li , I doubt you can add separate column at the end. You can replace % column using isfiltered or isinscope
You also check for Hybrid table work around
if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...
vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |