Add YTD Column in stacked column chart

[Gazsim44]

Hi, I'm currently looking to add an additional column for YTD totals to a chart that currently has Quarters on the x axis. The idea would be that the YTD column would sit after Q4 and would simply update as we go through the year with the full total count and % Performance. The quarter is coming from my date table with the column values all being generated from measures in my main data table.

Late = CALCULATE(DISTINCTCOUNT(Data[STT]), FILTER(Data,Data[Net Performance]="MISS"))
On Time = CALCULATE(DISTINCTCOUNT(Data[STT]), FILTER(Data,Data[Net Performance]="OK")) 
% Performance = DIVIDE(Data[Data Nett Shipments OK],Data[Data Total Shipments],0)
Quarter = CONCATENATE("Q",ROUNDUP(MONTH('Date'[Date])/3,0))

Many thanks

[Icey · Accepted solution]

Hi @Gazsim44 ,

Please try this:

1. Enter data to create a Columns table like below and then sort "Columns" column by "Order" column.

Note: no relationship between this new table and your data table.

Columns	Order
Q1	1
Q2	2
Q3	3
Q4	4
YTD	5

2. Create measures like so:

Late Measure =
VAR Columns_ =
    SELECTEDVALUE ( 'Columns'[Columns] )
RETURN
    SWITCH (
        Columns_,
        "YTD", CALCULATE ( [YTD], Data[Net Performance] = "MISS" ),
        CALCULATE ( [Late], Data[Quarter] = Columns_ )
    )

On Time Measure =
VAR Columns_ =
    SELECTEDVALUE ( 'Columns'[Columns] )
RETURN
    SWITCH (
        Columns_,
        "YTD", CALCULATE ( [YTD], Data[Net Performance] = "OK" ),
        CALCULATE ( [On Time], Data[Quarter] = Columns_ )
    )

3. Put 'Columns'[Columns] into "Axis" field and [Late Measure], [On Time Measure] into "Values" field to create a visual.

You can this post: Possible Analytics function: dynamic Total column in chart with USA States?

Not exactly the same, but the logic is the same.

Best Regards,

Icey

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

[amitchandak]

@Gazsim44 , With help from date table and Time intelligence you can try measure like one in example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
P

[Gazsim44]

@amitchandak Thanks for your reply although not sure this is what I need?

I am looking to add an additional column for YTD and not just an additional measure to add to the existing columns.

So in effect your columns would be Q1, Q2, Q3, Q4 and YTD, 

Thanks