Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Team,
Self had created new column as per Indian Fiscal year - months (Apr to Mar) & also added Fiscal year, half year & quarter.
Ex: Please find below data
Invoice Date-----FY Month ----FY Month Desc---FY Quarter...FY Half year....FY--Qty
10/04/2021 ---- 1 --------Apr-----Q1---- H1....FY22 ---100
10/05/2021 ---- 2 --------May-----Q1---- H1....FY22---110
10/06/2021 ---- 3 --------Jun-----Q1---- H1....FY22---90
10/07/2021 ---- 4 --------Jul-----Q2---- H1....FY22---150
10/08/2021 ---- 5 --------Aug-----Q2---- H1....FY22---120
10/09/2021 ---- 6 --------Sep-----Q2---- H1....FY22---140
10/10/2021 ---- 7 --------Oct-----Q3---- H2....FY22---160
10/11/2021 ---- 8 --------Nov-----Q3---- H2....FY22---180
10/04/2020 ---- 1 --------Apr-----Q1---- H1....FY21 ---70
10/05/2020 ---- 2 --------May-----Q1---- H1....FY21---110
10/06/2020 ---- 3 --------Jun-----Q1---- H1....FY21---90
10/07/2020 ---- 4 --------Jul-----Q2---- H1....FY21---160
10/08/2020 ---- 5 --------Aug-----Q2---- H1....FY21---100
10/09/2020 ---- 6 --------Sep-----Q2---- H1....FY21---110
10/10/2020 ---- 7 --------Oct-----Q3---- H2....FY21---150
10/11/2020 ---- 8 --------Nov-----Q3---- H2....FY21---80
10/12/2020 ---- 9 --------Dec-----Q3---- H2....FY21---120
10/01/2021 ---- 10 --------Jan-----Q4---- H2....FY21---110
10/02/2021 ---- 11 --------Feb-----Q4---- H2....FY21---80
10/03/2021 ---- 12 --------Mar-----Q4---- H2....FY21---90
10/04/2019 ---- 1 --------Apr-----Q1---- H1....FY20 ---80
10/05/2019 ---- 2 --------May-----Q1---- H1....FY20---100
10/06/2019 ---- 3 --------Jun-----Q1---- H1....FY20---90
10/07/2019 ---- 4 --------Jul-----Q2---- H1....FY20---110
10/08/2019 ---- 5 --------Aug-----Q2---- H1....FY20---100
10/09/2019 ---- 6 --------Sep-----Q2---- H1....FY20---110
10/10/2019 ---- 7 --------Oct-----Q3---- H2....FY20---130
10/11/2019 ---- 8 --------Nov-----Q3---- H2....FY20---90
10/12/2019 ---- 9 --------Dec-----Q3---- H2....FY20---110
10/01/2020 ---- 10 --------Jan-----Q4---- H2....FY20---100
10/02/2020 ---- 11 --------Feb-----Q4---- H2....FY20---110
10/03/2020 ---- 12 --------Mar-----Q4---- H2....FY20---110
Doubt 1:
Need Graph sorting as per financial year & month & quarter as per fiscal year
Qty should be average for
quarter (qty/3)
half year(qty/6)
year (qty/6)
& not sum
y-axis - Qty
x-axis - Default should show as per FY (Financial year)
If user clicks on specific financial year then should expand has H1 & H2 for that specific financial year.
Once user click on half year - should expand quarter wise
Once user click on quarter - should expand month wise
Note: Qty should be average
Example for X axis expansion
Jul Aug Sept Oct Nov
Q1 Q2 Q1 Q2 Q2
H1 H2 H1 H2
F20 FY21 FY22
Expansion in graph should happen only if click on Year/Half year/ Quarter till month
Also post clicking expansion should reduce to quarter/half/year.
Similar to Pivot.
Doubt 2:
Also confirm how to create table....NOTE: QTY SHOULD BE AVERAGE for quarter/half year/year.
Jul Aug Sept Oct Nov
Q1 Q2 Q1 Q2 Q2
H1 H2 H1 H2
F20 FY21 FY22
Please help in resolving.
Solved! Go to Solution.
Hi, @Anonymous
1. The extension of the quarter month of the fiscal year can be realized through the matrix view.
Fiscal year = RIGHT([FY Half year],4)
2. For qty around be average for quarter / half year / year, I created three measures to calculate.
AVERAGE for quarter =
CALCULATE (
AVERAGE ( 'Table'[Qty] ),
FILTER ( ALL ( 'Table' ), [FY Quarter] = MAX ( 'Table'[FY Quarter] ) )
)
AVERAGE for half year =
CALCULATE (
AVERAGE ( 'Table'[Qty] ),
FILTER ( ALL ( 'Table' ), [FY Half year] = MAX ( 'Table'[FY Half year] ) )
)
AVERAGE for year =
CALCULATE (
AVERAGE ( 'Table'[Qty] ),
FILTER ( ALL ( 'Table' ), [Fiscal year] = MAX ( 'Table'[Fiscal year] ) )
)
If the method I provided above can't solve your problem, what's your expected result? Can you use pictures to show your expected results? I hope I can help you and look forward to your reply.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
1. The extension of the quarter month of the fiscal year can be realized through the matrix view.
Fiscal year = RIGHT([FY Half year],4)
2. For qty around be average for quarter / half year / year, I created three measures to calculate.
AVERAGE for quarter =
CALCULATE (
AVERAGE ( 'Table'[Qty] ),
FILTER ( ALL ( 'Table' ), [FY Quarter] = MAX ( 'Table'[FY Quarter] ) )
)
AVERAGE for half year =
CALCULATE (
AVERAGE ( 'Table'[Qty] ),
FILTER ( ALL ( 'Table' ), [FY Half year] = MAX ( 'Table'[FY Half year] ) )
)
AVERAGE for year =
CALCULATE (
AVERAGE ( 'Table'[Qty] ),
FILTER ( ALL ( 'Table' ), [Fiscal year] = MAX ( 'Table'[Fiscal year] ) )
)
If the method I provided above can't solve your problem, what's your expected result? Can you use pictures to show your expected results? I hope I can help you and look forward to your reply.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Team,
Regret for delay in my response.
I want to populate Month Wise Data & Quartely data (in TPM) - 3 months qty/3.
Then apply graph for entire data set & filter quarters or months as required.
Self not able to attach excel sheet or take pic of graph
I had build seperate report in excel using sumifs & applied graphs. How to do similarly in power bi.
Also confirm how build report with sumifs or other formulas in one sheet & apply graphs for same data similar to excel
FY22 | FY22 | FY22 | FY22 | FY22 | FY22 | FY22 | FY22 | FY22 | ||||
Business Unit | Q4 FY21 | Apr | May | Jun | Q1 FY22 | Jul | Aug | Sep | Q2 FY22 | Oct | Nov | Dec |
BCM | 63033 | 53764 | 53498 | 59881 | 55714 | 63365 | 64076 | 66053 | 64498 | 66685 | 64489 | 75176 |
Kovai | 9300 | 9137 | 8536 | 7405 | 8359 | 9770 | 9534 | 9126 | 9477 | 9511 | 9380 | 11021 |
All of that is standard Matrix visual functionality. What have you tried and where are you stuck?