Forum Discussion

pminnov's avatar
pminnov
Icon for Helper II rankHelper II
1 year ago
Solved

problem with line graph for continuous totals

Hi, 

 

I've created a line graph to plot cumulative percent totals for people recruited to a committee across weeks but am running into an issue. I first created a calendar table to use to plot the data by week: 

 

Calendar =
VAR Days = CALENDAR ( MIN( mcrm_committee_member[Acceptance date]), MAX( mcrm_committee_member[Acceptance date]) )
RETURN ADDCOLUMNS (
    Days,
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "Day", FORMAT ([Date], "ddd"),
    "Month Year", FORMAT ( [Date], "mmm yy" ),
    "Week Number", WEEKNUM ( [Date],2))
 
I then created a measure to calculate the cumulative percent in terms of the number of people who have been recruited: 
 
% of recruited (cumulative) = calculate([% recruited against target],filter(ALLSELECTED('Calendar'), 'Calendar'[Date]<=MAX('Calendar'[Date])))
 
This is the resulting graph: 
 

 

The problem is that the cumulative total does not stop at the last data point for this particular committee which should be in March 2025 and instead runs until the last date in the entire data set for all committees (July 2025). 

 

I updated the cumulative total measure to incorporate a filter using the last date with values: 

 

% of recruited (cumulative) = calculate([% recruited against target],filter(ALLSELECTED('Calendar'), 'Calendar'[Date]<=MAX('mcrm_committee_member'[Acceptance date])))
 
The resulting line graph now has the correct date range on the x-axis but now the lines in the visual contain gaps instead of being continous: 
 

 

Is there a way to fix this and have the lines continuous as in the first chart but the x-axis align to the correct range as in the second graph? 

Need Help
  • pminnov's avatar
    pminnov
    1 year ago

    Thank you to everyone who posted suggestions.  I was able to determine that the issue that was causing the problem (gaps in lines) was due to using a weekmin date function (CALCULATE(MINX('Calendar','Calendar'[Date]),ALLEXCEPT('Calendar','Calendar'[Week Number]))) to graph my data on the x-axis. Power BI viewed this as a categorical value and gaps were created in my data as a result. When I changed the x-axis to continuous and instead used 'Date' from the Calendar table as the x-axis field the lines in the visual were presented without gaps over the correct timespan. I wasn't able to have the data presented weekly on the x-axis (it is presented monthly instead) but that is a small tradeoff I can live with. 

     

    Below is the result when the x-axis is continuous and Date is used from the calendar table: