YTD Calculation with count

[lm_suresh]

Hi 

Need graph like below.  Please help in BI 

X – Axis –  Up to 2024 (DEC), 2025 ( TILL JUNE) , 2025 planned ( June to Dec ), 2026 planned , 1960, 1950

Y axis – Count of Ctls

Legend – Status

Sample data Structure. 

Date ( dd /mm/yyyy)	Ctls	Status
1/1/1960	A	NA
1/1/1960	B	NA
1/1/1950	A	TBC
1/1/1950	B	TBC
1/1/1950	C	TBC
1/1/1950	D	TBC
1/1/2020	A	Completed
1/5/2020	B	Completed
1/1/2021	A	Completed
1/5/2021	B	Completed
1/1/2024	A	Completed
1/5/2024	B	Completed
1/6/2024	C	Completed
1/7/2024	D	Completed
1/1/2025	A	Completed
1/5/2025	B	Completed
1/3/2025	C	Completed
1/2/2025	D	Completed
1/4/2025	A	Completed
1/9/2025	E	Planned - 2025
1/10/2025	A	Planned - 2025
1/10/2025	B	Planned - 2025
1/10/2025	C	Planned - 2025
1/10/2025	D	Planned - 2025
1/10/2025	C	Planned - 2025
1/10/2026	A	Planned - 2026
1/10/2026	B	Planned - 2026
1/10/2026	C	Planned - 2026
1/10/2026	D	Planned - 2026
1/10/2026	C	Planned - 2026

[v-echaithra]

Hi @lm_suresh ,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[DataNinja777]

Hi @lm_suresh ,

To get the graph you want, you first need to create a new calculated column in your BI tool. This column will act as a category for your X-axis by grouping each record into the specific timeframes you requested. The logic for this new column, let's call it "Timeframe," will check the 'Date' and 'Status' of each entry and assign it to the correct group, such as "1950", "Up to 2024 (DEC)", or "2025 (TILL JUNE)".

If you are using a BI tool like Power BI, you can create this column using a DAX formula. This formula uses a SWITCH function to evaluate each condition in order and assign the correct text value to your new Timeframe column for every row in your data table.

Timeframe = 
SWITCH (
    TRUE (),
    YEAR ( [Date] ) = 1950, "1950",
    YEAR ( [Date] ) = 1960, "1960",
    YEAR ( [Date] ) <= 2024, "Up to 2024 (DEC)",
    YEAR ( [Date] ) = 2025 && MONTH ( [Date] ) <= 6, "2025 (TILL JUNE)",
    YEAR ( [Date] ) = 2025 && MONTH ( [Date] ) > 6, "2025 planned (July to Dec)",
    YEAR ( [Date] ) = 2026, "2026 planned",
    "Other"
)

After creating this Timeframe column, building the visual is simple. Choose a stacked or clustered column chart. Drag your new Timeframe column to the X-axis and the Ctls field to the Y-axis, ensuring its aggregation is set to Count. Finally, to segment the bars by status, drag the Status field to the Legend area. This will generate the exact chart you're looking for, with each bar representing a timeframe and its colors showing the count of different statuses within it.

Best regards,

[tharunkumarRTK · Accepted solution]

@lm_suresh 

1. create a calcuated column 

PeriodBucket = 
SWITCH(
    TRUE(),
    YEAR([Date]) = 1950, "1950",
    YEAR([Date]) = 1960, "1960",
    [Date] <= DATE(2024,12,31), "Up to 2024 (DEC)",
    [Date] <= DATE(2025,6,30), "2025 (TILL JUNE)",
    [Date] <= DATE(2025,12,31), "2025 Planned (June to Dec)",
    [Date] <= DATE(2026,12,31), "2026 Planned",
    "Other"
)

put this column in x axis 

2. Create a DAX measure 

Ctls Count = COUNTROWS('YourTableName')

put this measure in y axis 

3. Put status column in legend field 

thats it

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!