Clustered column & 2 lines with same series (TY & LY)

[Ailis]

Hi,

I am new to Power BI and trying to create a report. I have an excel chart based on the same data (solid line = TY, dotted line = LY):

I am trying to replicate this in PowerBI. Currently my chart in PowerBI looks like this:

PowerBI

I cant work out how to include a TY and LY line, rather than a line which is just the total of both. I am not sure if I need to pivot my data/create a measure/something else?

Currently to only show the line for a specific company I am filtering in the chart filters, but I may also eventually want to add multiple lines with other company TY data too, as below:

Many thanks!

[v-haibl-msft · Accepted solution]

@Ailis

Yes, you need to create a measure. You can take a look at this article. I’ll also give a simple sample as below.

BTW, I’ve uploaded my .pbix file of following sample to here for reference.

For above dataset, we can create a measure with following DAX formula to get the last year sales.

The calendar table is related with above table using Date key.

LY_Sales = 
CALCULATE ( SUM ( Table1[Sales] ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

Then in the line and clustered column chart, we can drag both LY_Sales and TY_Sales into Line values.

Best Regards,

Herbert