I have 3 tables - one has events of which there are several types, one has hours and I have a calendar table to tie it all together.
I have created a measure to cumulatively sum my events by date and another to cumulatively sum my hours by date.
On their own these measure work perfectly and plot on visuals even when they flatline in periods where there are no new events (there is always hours but not always events in each month).
I have created a third measure which uses these measures to calculate a frequency rate (FR) which again works perfectly when I want an overall FR.
However the overall FR is not very useful and you would rather see the FR for the different event types. I want to make my visuals interactive by using a slicer to select which event type of event you are looking at. The problem occurs that my visuals will only show the months for when the specific events take place instead of showing the full trend. The visuals are all showing the correct numbers but if for example in the first 4 months of the year I get 3 events and then get no events for the rest of the year, the hours would continue to accumulate and improve my FR month on month. My visuals will not show this trend. Can I do something to my measures or visuals to produce the desired result.
I hope my picture helps. I know the problem can be seen in the tables in the bottom half of the picture - the events in their categories acumulate correctly in the months they happen but it does not give the same value for the next month where no month occurs therefore my third measure only calculates for the event types when the hours cross over with them.