cancel
Showing results for
Did you mean:
Regular Visitor

past year and ytd on same chart

Trying to replicate this chart in power bi.

The rolling 12 month rate is a calculation of two columns in different tables. Counts of events divided by hours over same period.

I also have a date table DimDateOccured and place table with placecode, placename

Tables look like

details

 DateOccured Event Placecode 12/5/2016 0 a 11/6/2017 1 a 22/12/2016 1 b 3/1/2015 0 c

Hoursworked ( only one record each month for each place)

 Date hours Placecode 1/5/2016 50 a 1/6/2017 52 a 1/12/2016 40 b 1/1/2015 10 c

I have measures for rolling 12 months for hours and events,

Hours12M = CALCULATE (

SUM(HoursWorked[Hours]),

DATESBETWEEN (

DimDateOccured[date],

SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( DimDateOccured[date] ) ) ),

LASTDATE ( DimDateOccured[date] )

)

)

and events12m which work for months or year.

Events12M = CALCULATE (

count(details[event]),

DATESBETWEEN (

DimDateOccured[date],

SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( DimDateOccured[date] ) ) ),

LASTDATE ( DimDateOccured[date] )

)

)

How can I combine them on same graph?

What is the best way to limit the date range displayed to valid values as rolling 12 months works for next 12m as the values are correct but invalid without data.?

2 REPLIES 2
Community Support

Re: past year and ytd on same chart

Hi

To combine the two measure in a line-stack chart, just drag the measures to the value field. To modify the date range, you can use visual level filter to set the range for your chart.

Regards,

Jimmy Tao

Regular Visitor

Re: past year and ytd on same chart

hi jimmy,

the date range is both year for past years and month for the past 12month. how to you create a visual filter for this ?

thanks

Mark

Announcements

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors