Connecting line between gaps in a clustered line & bar chart?

[MacJasem]

I've got a line in my clustered line & bar chart that has gaps. i wish the line could be connected between gaps:

the orange line as you can see has gaps and i would like to know a way to have the line be continous over the dates with no values. is that possible?

this is the measure behind the orange line:

Realized = 

VAR LastApprovalDate = CALCULATE(MAX('Documents'[Planned Approval Date]), 'Documents'[Status] = "Approved")

VAR CurrentDate = MAX('DateTable'[Date])

VAR PreviousValue =

    CALCULATE(

        COUNT('Documents'[Status]),

        FILTER(

            ALL('DateTable'),

            'DateTable'[Date] < CurrentDate && 'DateTable'[Date] <= LastApprovalDate

        )

    )

RETURN

IF(

    CurrentDate <= LastApprovalDate,

    CALCULATE(

        COUNT('Documents'[Status]),

        FILTER(

            ALLSELECTED('DateTable'[Date]),

            'DateTable'[Date] <= LastApprovalDate

        )

    ),

    PreviousValue

)

[v-bmanikante]

Hi @MacJasem ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[v-bmanikante]

Hi @MacJasem ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[v-bmanikante]

Hi @MacJasem ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[DataNinja777]

Hi @MacJasem ,

You can make the orange line continuous in your clustered line & bar chart in Power BI by ensuring that missing values are filled with the last available value. Since Power BI does not automatically interpolate missing data points in a line chart, the best approach is to modify your DAX measure to return the last known value instead of BLANK(). This way, the line remains continuous even if there are gaps in the data.

To do this, update your Realized measure by implementing a forward-fill logic. This modified measure ensures that if a value is missing for a given date, the previous available value is carried forward.

Realized = 
VAR LastApprovalDate = CALCULATE(MAX('Documents'[Planned Approval Date]), 'Documents'[Status] = "Approved")
VAR CurrentDate = MAX('DateTable'[Date])
VAR PreviousValue =
    CALCULATE(
        COUNT('Documents'[Status]),
        FILTER(
            ALL('DateTable'),
            'DateTable'[Date] < CurrentDate && 'DateTable'[Date] <= LastApprovalDate
        )
    )
VAR CurrentValue =
    CALCULATE(
        COUNT('Documents'[Status]),
        FILTER(
            ALLSELECTED('DateTable'[Date]),
            'DateTable'[Date] <= LastApprovalDate
        )
    )
RETURN
IF(
    ISBLANK(CurrentValue),
    PreviousValue,  
    CurrentValue
)

This measure ensures that whenever there is no data for a given date, it returns the last available count instead of leaving a gap. Additionally, adjusting the X-Axis settings in the Format Pane can further help in smoothing the visualization. Ensure that "Show Items with No Data" is enabled, and set the X-Axis type to "Continuous" instead of "Categorical" if your date field supports it. This prevents breaks in the line and provides a smoother trend across time. Let me know if you need further refinements.

Best regards,

[MacJasem]

Thanks @DataNinja777

I'm not seeing the "Show items with No data" option nor the "Continuous" option. Could you perhaps show me screenshots of where i find those options cause i can't see/find them 🙂  

[Anonymous]

HI @MacJasem,

Have you tried to add +0 to you expression to force these calculations on all the axis field ranges?

Regards,

Xiaoxin Sheng

[MacJasem]

Thanks @Anonymous 

Can you perhaps elaborate or give an example of where and how to add the expression?

[Anonymous]

HI @MacJasem,

The +0 will force the calculation on blank value Dax range to return zero instead blank. By default power bi visuals will hide the blank part in visualization if they have not corresponded values. 

BTW, your formula is calculated on range, so +0 will look up the previous value to instead the zero.

How to return 0 instead of BLANK in DAX - SQLBI

Regards,

Xiaoxin Sheng