Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Navy_
Regular Visitor

Cumulative of previous year quarters in to one bar and current quarters in to individual bars

Hi,

 

I have a client which they follows financial year from April to March of every year. I have a column in which having the below mentioned entries for now. It could be added with new entries of AMJ'25,JAS'25,OND'25,JFM'26 once FY-24/25 gets completed. And it goes on like this.

 

Problem Area: I have to show the column graph in which xaxis should be like FY-23/24, AMJ'24, JAS'24,OND'24,JFM'25 of each column for this year once this Financial year.

 

Once FY-24/25 financial year gets completed then automation xaxis values of AMJ'24,JAS'24,OND'24,JFM'25 needs to be get clubbed in to one column which should be shown on graph as FY-24/25 and next financial year of FY-25/26 needs to shown in individual columns like FY-23/24,FY-24/25,AMJ'25,JAS'25,OND'25,JFM'26 on xaxis.

 

Once FY-25/26 financial year gets completed then automation xaxis values of AMJ'25,JAS'25,OND'25,JFM'26 needs to be get clubbed in to one column which should be shown on graph as FY-25/26 and next financial year of FY-26/27 needs to shown in individual columns like FY-23/24,FY-24/25,FY-25/26,AMJ'26,JAS'26,OND'26,JFM'27 on xaxis.

 

Please find the below attached sample dataset column:

 

Financial Year
FY-23/24
AMJ'24
JAS'24
OND'24
JFM'25

6 REPLIES 6
Navy_
Regular Visitor

Sure 

rajendraongole1
Community Champion
Community Champion

Hi @Navy_ - Can you share your sample pbix file to look over.

 

Below is the dataset. 

 

step 1: I have to create a new calculated column in which we have to show values based on projected quarter, completed quarter and coverage year columns

step 2: I have to focus on FY-23/24 from coverage year column , where ever FY-23/24 is present we have to shown as it as in calculated column(which have to create new)

step 3: I have to give 1st priority for the completed quarter if we have value in completed quarter it should return complete quarter other project quarter in calculated column(which have to create new)

step 4: Now after creating of calculated column, have to represent the dataset on column chart in which xaxis should have of the data as per the calculated column which created based on above conditions.

step 5: here is the another challenge FY-23/24 we have only one entry so we will show it is in one bar, until FY24/25 running each quarter of FY24/25 should shown as individual bars, once FY24/25 get complete each quarter of FY-24/25 should be get clubbed in to one bar which is of FY-24/25, which means post completing of FY24/25 we have 2 bars on xaxis those are FY23/24, FY24/25 along with this FY-25/26 individual quarter bars which means FY23/24 , FY24/25, AMJ'25,JAS'25,OND'25,JFM'26

 

But thisdata  FY23/24 , FY24/25, AMJ'25,JAS'25,OND'25,JFM'26 should be shown only when FY25/26 enters until graph should show only FY23/24,AMJ'24,JAS'24,OND'24,JFM'25

 

Below is the dataset. I was trying to attach the PBIX file by inserting link. Hope it wont work. I'm so sorry as i'm new to community i dont know how to attach the files in reply or by sending new message. 

 

These are the column headers Account, SL, TT, Projected QUarter, Completed Quarter, COverage Year

AABCEF  FY-23/24
ADEFGHAMJ'24AMJ'24FY-24/25
A
A
GHIIJJAS'24AMJ'24FY-24/25
BABCEF  FY-23/24
BDEFGHAMJ'24 FY-24/25
BGHIIJAMJ'24 FY-24/25
CABCEFJAS'24 FY-24/25
CDEFGHOND'24 FY-24/25
CGHIIJJFM'24 FY-24/25
DABCEF  FY-23/24
D
D
DEFGH  FY-23/24
DGHIIJJFM'25 FY-24/25
EABCEFAMJ'25AMJ'25FY-25/26
EDEFGHJAS'25 FY-25/26
EGHIIJOND'25AMJ'25FY-25/26
FABCEFJFM'26 FY-25/26
FDEFGHAMJ'25 FY-25/26
FGHIIJJAS'25 FY-25/26
Navy_
Regular Visitor

Hi, 

 

Can anyone help me in this problem.

Navy_
Regular Visitor

It is not working 

rajendraongole1
Community Champion
Community Champion

Hi @Navy_ -Create a custom column that can adjust based on the completion of financial years

as below

Financial Year Group =
IF(
LEFT([Financial Year], 2) = "FY",
LEFT([Financial Year], 7),
CONCATENATE(
"Q",
SWITCH(
LEFT([Financial Year], 3),
"AMJ", "1",
"JAS", "2",
"OND", "3",
"JFM", "4",
BLANK()
) & " " & RIGHT([Financial Year], 2)
)
)

 

Create a measure to check if the financial year

Is Completed FY =
IF(
LEFT(MAX('YourTable'[Financial Year]), 2) = "FY",
IF(
VALUE(RIGHT(MAX('YourTable'[Financial Year]), 2)) < YEAR(TODAY()),
1,
0
),
0
)

 

create another measure to group the FY

Dynamic Financial Year Group =
IF(
[Is Completed FY] = 1,
LEFT(MAX('YourTable'[Financial Year]), 7),
'YourTable'[Financial Year Group]
)

Lastly create a calculated column for the axis label

Axis Label =
IF(
[Is Completed FY] = 1,
LEFT(MAX('YourTable'[Financial Year]), 7),
CONCATENATE(
"Q",
SWITCH(
LEFT([Financial Year], 3),
"AMJ", "1",
"JAS", "2",
"OND", "3",
"JFM", "4",
BLANK()
) & " " & RIGHT([Financial Year], 2)
)
)

 

Hope it works 

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.