Cumulative Count of Data based on Current and previous Year Quarters

[Navy_]

Hi All,

Please help me in resolution of my problem.

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 this data  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. 

Account	SL	TT	Projected Quarter	Completed Quarter	Coverage Year
A	ABC	EF			FY-23/24
A	DEF	GH	AMJ'24	AMJ'24	FY-24/25
A
A	GHI	IJ	JAS'24	AMJ'24	FY-24/25
B	ABC	EF			FY-23/24
B	DEF	GH	AMJ'24		FY-24/25
B	GHI	IJ	AMJ'24		FY-24/25
C	ABC	EF	JAS'24		FY-24/25
C	DEF	GH	OND'24		FY-24/25
C	GHI	IJ	JFM'24		FY-24/25
D	ABC	EF			FY-23/24
D
D	DEF	GH			FY-23/24
D	GHI	IJ	JFM'25		FY-24/25
E	ABC	EF	AMJ'25	AMJ'25	FY-25/26
E	DEF	GH	JAS'25		FY-25/26
E	GHI	IJ	OND'25	AMJ'25	FY-25/26
F	ABC	EF	JFM'26		FY-25/26
F	DEF	GH	AMJ'25		FY-25/26
F	GHI	IJ	JAS'25		FY-25/26

[Anonymous]

Hi @Navy_ ，hello @rajendraongole1 Thank you for your prompt reply!

For your requirements, please create two calculated columns as shown below:

In my sample, I understand that completed quarters and projected quarters are blank if the year has already

occurred.

CalculatedColumn =

IF(

   'Table'[Completed Quarter]=BLANK() && 'Table'[Projected Quarter]=BLANK(),

   'Table'[Coverage Year],

   IF(

       'Table'[Completed Quarter]=BLANK(),

     'Table'[Projected Quarter],

      'Table'[Completed Quarter]

   )

)

Then ,create another column to display the chart:

ChartColumn =

    VAR FY25_26 = "FY-25/26"

    VAR FY24_25 = "FY-24/25"

    VAR CompletedQtr24_25 =

        CALCULATE(

            COUNTROWS('Table'),

            FILTER(

                'Table',

                'Table'[Coverage Year] = FY24_25 && 'Table'[Completed Quarter]=BLANK() && 'Table'[Projected Quarter]=BLANK())

            )>0

    VAR CurrentQtr = [CalculatedColumn]

    RETURN

        IF(

            'Table'[Coverage Year] = "FY-23/24",

             [CalculatedColumn],

            IF(

                CompletedQtr24_25,

                IF(

                    'Table'[Coverage Year] = FY24_25,

                    [CalculatedColumn],

                    CurrentQtr

                ),

                CurrentQtr

            )

        )

If the FY-24-25 has been completed, the chart will show like this, remember to filter the blank value in chart column:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Navy_]

Hi, 

Just for my clarification.... It means one fy24/25 gets completed then only data will display like the same in the second graph.

Correct me if I'm wrong...

[Anonymous]

Hi @Navy_,

As I understand it, if FY24/25 is completed, the chart will show two bars for FY23/24, FY24/25, and the uncompleted FY25/26 will show the detailed projected quarterly values.

Please feel free to reply if I have misunderstood your question.

[Navy_]

Here we are having for fy23/24 once the Fy-24/25gets completed like fy23/24 all quarters data should be shown as in one bar expected result on xaxis like fy23/24,fy24/25,amj'25,,jas'25,ond'25,jfm'26

Like this it should be automated for all years 

[rajendraongole1]

Hi @Navy_ - Can you try the below calculated column

CalculatedColumn =
VAR ProjQuarter = 'Data'[Projected Quarter]
VAR CompQuarter = 'Data'[Completed Quarter]
VAR CoverageYear = 'Data'[Coverage Year]

RETURN
IF(
CoverageYear = "FY-23/24", "FY-23/24",
IF(
NOT ISBLANK(CompQuarter), CompQuarter,
ProjQuarter
)
)

Ensure your data is sorted correctly in the calculated column to reflect the chronological order of fiscal years and quarters.

Hope it helps

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

[Navy_]

I really appreciated your efforts but is not working.