Add LYTD and YTD as columns to bar chart that has a month x-axis?

[tonyclifton]

Hello community,

is it possible to recreate the following Excel bar chart in Power Bi? The problem I have is to put the Last YTD, YTD, and Budget bars to the Power Bi chart (see below).

Excel

So in Power Bi I use a Line and Stacked Colum Chart.

The column value is colored (green or red) based on a column in the Column Series field that checks wether the current value is below or above the Budget. In this example no red exists.

I then created two measures for LYTD and YTD to at least use them in the Line Values section:

Measure_LYTD = CALCULATE(
	SUM(Column_AVG);
	PARALLELPERIOD('Calendar'[Date];-1;MONTH);'Calendar'[MonthNumber] = 12)
	
Measure_YTD = CALCULATE(
	SUM(Column_AVG);
	'Calendar'[MonthNumber] = 12
)

Is there any way to add the currently three line values as column bars to the chart? Maybe you have a link to an example.

Thanks alot in advance.

[Greg_Deckler]

One way would be to create a separate table that included my month names as well as YTD and Budget. Use that as the axis and then simply write a measure that checks that value and returns the appropriate calculation depending on what month it is in or if it is in YTD or Budget.

[Anonymous]

Greg, How is a relationship created between the YTD in the seaprate table and the data table?

[tonyclifton]

Thank you Greg for your answer. However I am too new to Power Bi to exactly undertand what I need to do.

I created a sample pbix file and would be happy if you could show the needed steps there.

PBIX Download

Thank you.

[Anonymous]

I've been tried about this.its hard to match the need.

CAUSE OF you YTD needs calendar to filter the measure, BUT your other axis Value don't need the Filter.

even you accomplish the needs, I think it will be hard coding.

So in my opinion that you need to re-overview your need.

in my guess , draw a line to compare every month sale is a better way.

(like drawing 6 sigma lines) 

update :

here's something I tried.

but year number is hard coded, and measure can't filtered by year column.

Measure = 

VAR A = VALUES('Table1'[A])

RETURN
SWITCH(
       TRUE(),
      A ="2017" || A="2018" ,CALCULATE(SUM(Master[Sales]),TREATAS(VALUES('Table1'[A]),'Calendar'[Year])),
     A IN VALUES('Calendar'[MonthNumberText]) ,TOTALYTD(SUM(Master[Sales]),'Calendar'[Date],TREATAS(VALUES('Table1'[A]),'Calendar'[MonthNumberText])),
     A = "BG", CALCULATE(SUM(Master[Budget]),ALL('Calendar')),
        0
)

[tonyclifton]

Thank you very much for your effor @Anonymous

Too bad the years have to be hardcoded.

Maybe someone else has another idea, as this is the prefered way of displaying the values.

[Anonymous]

The easy solution is to do it in two visuals and just align the Y-axis and hide it for the visual to the right.