Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Create YTD Column Chart

Hello community,

I want to create a YTD column chart like excel graph:

I use 2 table: Actual 2023 is everything about financial statement and custom table for display date: 

I try to follow the tutorial from yt video but it not display the ytd column, here is my visual: 

With X-axis is Period, Y-axis is Custom Catering Profit and Legend is Catering Group. Below is dax for create Custom Catering Profit.

Custom Catering Profit 4 =
SWITCH (
    TRUE (),
    // Previous year
    // SELECTEDVALUE('Time Intelligence'[Order]) = -2,
    // CALCULATE(
    //     COUNTROWS('Actual 2023'),
    //     FILTER(
    //         ALL('Actual 2023'),

    // Months
    SELECTEDVALUE('Time Intelligence'[Order]) > 0
        && SELECTEDVALUE('Time Intelligence'[Order]) < 13,
        CALCULATE (
            COUNTROWS('Actual 2023'),
            FILTER(
                ALL(MG_D_DATE_V),
                MG_D_DATE_V[BUSINESS_MONTH] = SELECTEDVALUE('Time Intelligence'[Order])
            ),
            FILTER(
                'Actual 2023',
                [% Actual Store Profit After H.O Fee 2023] >= -5 &&
                [% Actual Store Profit After H.O Fee 2023] <= 5
            )
        ),
    // YTD
    SELECTEDVALUE('Time Intelligence'[Order]) = 13 ,
        CALCULATE(
            COUNTROWS('Actual 2023'),
            FILTER(
                'Actual 2023',
                [% Actual Store Profit After H.O Fee 2023] >= -5 &&
                [% Actual Store Profit After H.O Fee 2023] <= 5
            ),
            FILTER(
                ALL(MG_D_DATE_V),
                MG_D_DATE_V[Year] = MAX(MG_D_DATE_V[Year])
            )
))
)
I think my measure was wrong in YTD because i can display month in my visual. 
In my case, I want to count store in a group of catering profit, the catering profit = profit/net sale. 
My case was very challenge because when i display month and ytd, I need to create 2 differnt group in legend and I dont know how to do it. Here my table: My case was very challenge because when i display month and ytd, I need to create 2 differnt group in legend and I dont know how to do it. Here my table:
 

It not like in the yt video that only calculate net sale, my case is when i can calculate what i want, the visual must show the number of store in different group. 

Hope someone can help me, if you don't understand my case, feel free to ask me.

Best regards

 

3 Replies

  • repeating the same data in the same visual is a design red flag.  Use two visuals.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hello lbendlin,

      Thank you for you reply but my manager want to see both in one visual so is it any way to do it?