Visual to plot all data points for a cumulative measure when there is no data for some periods?

[Anonymous]

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.

 

[v-diye-msft]

Hi @Anonymous 

Sorry, I don't quite understand what you're looking for. 

You might consider creating a pbix file that will contain some sample data (Not just screenshot), upload the pbix to one drive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[Anonymous]

The result I want is relating to my bottom left table/measure to show an answer for each month. Take for example the "near miss" category - I have 1 in Feb and then another in Mar making it 2 but then I dont have another until July making it 3. I want my table to show:

Jan - 0

Feb - 1

Mar - 2

Apr - 2

May - 2 

Jun - 2

Jul - 3

Aug - 3

Sep - 3

Just like how the line chart above it is representing the data. It even displays the tool tip for the correct amount when the measure flat lines.

I need this because the FR measure on the right is the same - it calculates the correct FR for the event types but when I use a slicer for the event types, my visual will only show me the months where there is a data entry just like displayed in the table on the bottom right.

The reality is the FR should display in every month because if I have no more incidents in a month where I do have hours (which is every month) then my FR goes down which is good. For example the 3rd measure used on the right in the screen shot is (cumulative events / cumulative hours) * 100,000 - the entry for July and "near misses" is correct at 9.20 but by september it should be (3/44620)*100,000 = 6.72