problem with line graph for continuous totals

[pminnov]

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? 

[danextian]

Hi @pminnov 

Try the following measure:

Cumulative Value with a Stop = 
VAR _MaxDate =
    MAX ( tbl[Date] )
RETURN
    CALCULATE (
        SUM ( 'tbl'[Sales] ),
        FILTER ( ALL ( Dates ), Dates[Date] <= _MaxDate )
    )

Please see the atahced pbix

[pminnov]

Unfortunately it didn't work. I use the following DAX to calculate the cumulative percent: 

% recruited (cumulative) =
CALCULATE (
    [% recruited against target],
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

This formula works but the issue is the calendar date (the maximum date) becomes the last date in the column of the data table I'm referencing. I apply a filter in the DAX related to calendar date (in bold below):

% of recruited reviewers (cumulative) =
CALCULATE (
    [% recruited against target],
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'mcrm_committee_member'[Acceptance date] )
    )
)

This results in causing gaps in the data table where cumulative values should appear. 

The date column I'm referencing contains blank values and I'm not sure how to accomodate this. 

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

In your measure determine the max date for each committee, and return BLANK() if the "current" date is larger.

[FBergamaschi]

To help you, two things are needed

1 - Please turn the graph into tables so we can see numbers

2 - 

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[pminnov]

I'm working on trying to put together a sample file that I can post. In the meantime, I noticed that when I use a slicer that contains a column of names that are used in the legend field of the visual and I select a name, the line is continuous and works. When I select more than one name, the lines become broken. 

Visual when one name is selected in the slicer: 

Visual when more than one name is selected in the slicer: 

[pminnov]

Hi, I've narrowed down the issue that is causing the problem. The line chart visual behaves as intended (the x-axis corresponds to the correct start date (Nov 2) and end date (March 31)) when I graph the total value of the % of recruited (cumulative) measure that I described earlier. 

Total value of % of recruited (cumulative) - works as intended

However, when I break down that total in the visual by placing a column of names (a data table column called 'point of contact') as the field in the legend for the visual it results in the data being presented by each name but gaps appear in the lines when there shouldn't be (see screengrab below).

Total value of % of recruited (cumulative) with point of contact column added to legend 

- does not work as intended (there are gaps in the lines)

As a test, I created a few measures for select names from that 'point of contact' data table column and added those to the y-axis field (and left the legend field blank )and the visual worked as intended with no gaps (see screengrab below). For example:

Natalee =
CALCULATE (
    [% recruited against target],
    'mcrm_committee'[point of contact] = "Natalee",
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'mcrm_committee_member'[Acceptance date] )
    )
) 

Total value of % of recruited (cumulative) with point of contact measures added to y-axis

- works as intended

To summarize, using a column of text data (names) in the legend field of the line chart results in gaps in the lines that shouldn't appear (since it plots cumulative percentages over time) but using a measure in the y-axis field for each name (instead of the data column containing names) does not.

Ideally, since there are a lot of names I would rather use the data column containing the names in the legend field as the preferred approach. Any ideas on why this error is happening and how to fix it? 

[pminnov]

To follow up, I tabled the data (raw counts and cumulative percent values by point of contact by week) and Power BI isn't calculating values at every data point. In the two tables below, the rows are each point of contact and the column header is week number. The first table is a count of the number of people who accepted and the below table is the same except the values are cumulative percents. Power BI isn't calculating a cumulative percent value for each week - only when there is a non-blank value. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[pminnov · Accepted solution]

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: