cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ozhug Regular Visitor
Regular Visitor

past year and ytd on same chart

Trying to replicate this chart in power bi.

ytd+pastyears.PNG

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
Community Support

Re: past year and ytd on same chart

Hi ozhug,

 

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

ozhug Regular Visitor
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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors