Forum Discussion
Anonymous
2 years agoNot applicable
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