Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ozhug
Advocate II
Advocate II

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
v-yuta-msft
Community Support
Community Support

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.