cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Visuals & measures in months without data

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

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Visuals & measures in months without data

Hi GolaubT,

I can't reproduce your design just based on your description, so if possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: Visuals & measures in months without data

Hello

 

Event Table - Date of event/Event count/Event suptype

Hours Table - MonthID (Format ex 201903 for March 2019)/Hours

 

I have a Calendar table connecting to both tables to give date context to the different fields.

 

I have one measure which is Cumulative Sum of /Event count/. I have another measure which is Cumulative Sum of /Hours/. Both these measure work as expected on a line visual, start at zero in Jan and show the change through to Sep.

 

I have Hours every month but I dont always have events. The problem is when I use a filter for /Event suptype/ the visuals will show the correct data, however they will only show the months where there is that kind of /Event suptype/. So my visuals dont display my FR improving becuase there are more hours cumulatively and no events but if I throw in another fake event it will still calculate it correctly for the cumuative totals. 

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.