Forum Discussion

lm_suresh's avatar
lm_suresh
Frequent Visitor
1 year ago
Solved

YTD Calculation with count

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)CtlsStatus
1/1/1960ANA
1/1/1960BNA
1/1/1950ATBC
1/1/1950BTBC
1/1/1950CTBC
1/1/1950DTBC
1/1/2020ACompleted
1/5/2020BCompleted
1/1/2021ACompleted
1/5/2021BCompleted
1/1/2024ACompleted
1/5/2024BCompleted
1/6/2024CCompleted
1/7/2024DCompleted
1/1/2025ACompleted
1/5/2025BCompleted
1/3/2025CCompleted
1/2/2025DCompleted
1/4/2025ACompleted
1/9/2025EPlanned - 2025
1/10/2025APlanned - 2025
1/10/2025BPlanned - 2025
1/10/2025CPlanned - 2025
1/10/2025DPlanned - 2025
1/10/2025CPlanned - 2025
1/10/2026APlanned - 2026
1/10/2026BPlanned - 2026
1/10/2026CPlanned - 2026
1/10/2026DPlanned - 2026
1/10/2026CPlanned - 2026

 

 

Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    1 year ago

    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!

3 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    1 year ago

    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!
  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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,

  • v-echaithra's avatar
    v-echaithra
    Community Support
    1 year ago

    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.