Graphing aggregated weekly data

[SteveW_Stats]

Hi all

I'm trying to create a graph of bugs on our system on a bugs/week basis. So the graph should show a list of every Monday on the x-axis and a count of bugs on the y-axis.

I've created a table that contains every Monday - I've done this as I've had problems with the graph excluding Mondays that have no associated bugs.

My data looks like this:

Some weeks have no bugs raised, some have many.

I then added a Measure in the Visualisations table:

With Measure "BugCount1" my VisualisationTable has either correct, positive, counts or blank values alongside the weeks that have no bugs raised - it looks sensible, but the graph skips the weeks that have blanks; I want the graph to show all the weeks including the empty ones.

If I include the Measure "BugCount2" my VisualisationTable expands to include every date, not just Mondays, and puts a zero next to all of the blank dates (the positive values are still correct). The graph's x-axis now displays a column for every day (most of which are empty, of course).

Can someone advise how I can get a nice clear graph that with just columns for each Monday date, please.

Thanks 🙂

[Anonymous]

Hi @SteveW_Stats ,

Please have a try.

Create a Date Table: If you haven't already, create a Date table with a column for dates and ensure it contains all possible dates, not just Mondays. This table will help us to make sure that every week is represented in your report.

Create a calculated Column for Mondays:

IsMonday = IF(WEEKDAY([DateColumn], 2) = 1, TRUE, FALSE)

Replace with the actual column name containing the dates in your Date table.[DateColumn]

Create Relationships: Build a relationship between your Date table and your Bugs table using the date columns.

BugCount = CALCULATE(COUNT('YourTable'[BugID]), 'DateTable'[IsMonday] = TRUE)

This measure will count the number of bugs for the dates marked as Mondays.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[gadielsolis]

Hello,

Add +0 at the end of the measure BugCount1:
BugCount1=Countrows(BugTable)+0

[SteveW_Stats]

Thanks for your response, but this just caused the full list of dates (not just Mondays) to be displayed; much like my BugCount2 measure above.