I have a report I am working on with various tables. I have 1 table that has events with dates in them, there a few different kinds of events. I have a second table that has hours worked for each project along with the month they were worked in. I also have a calendar table which has a relationship with the other 2 tables.
From my Events table, I created a measure - cumulative sum events month on month.
In my Hours table I have a measure - cumulative sum hours month on month.
I have then used the above 2 measures to create a third measure, call this frequency rates, ( Events/Hours)*100,000 - this third measure also works great.
The problem I am having is that when I throw it onto a visual to show how it changes I can make it do one of two things. I am using a line visual by the way and in my data there is always Hours in a month but there is sometimes no events.
Firstly it will plot only points for months where events happen. It shows the correct frequency rate based on the cumulative hours and events but say I have an event in Feb and Mar I will get a point for each and a line in between but then I dont have another event until July I will only get a point for July and no line in between.
The second thing I can do is change the X-Axis to categorical and it will show all my months with lines joining but I will only get an updated tooltip on months where the event occurs.
This is particularly frustrating because I am using slicers to filter the visuals by event type, the visual changes to reflect the correct Frequency Rate but for example Event Type B hasn't occured since March 2018 so my visual only goes to March 2018 and does not show me what my current Frequency Rate is in September - which should be lower (better) as more hours have been accumulated but no events have.
Any idea how I can solve this problem so that my visual has a tooltip for every month and goes to my most recent month.
thanks
