Why is a '(Blank)' value being returned in a date axis?

Question

I have a chart displaying both bars to show how many items fall on a given date and lines to show the cumulative count of items over time.

I believe my error lies with how the cumulative count is being derived, but I can't pin point where.

The only other area I can think the issue might be occurring is within the relationships between the 'ROPcertTracker' and 'ROP MEP PCC CERT Date Range' tables, whereby because the two 'Actual Submission Dates' are before the Forecast dates, the cumulative measure can't determine the correct result.

It's odd that the cumulative measure does appear to display the correct result as well as the erroneous one though.

I have triple checked the source data and have tried some changes to the cumulative measure, but to no avail.

Headers of the source table ('ROPcertTracker');

Discipline (populate with 'MEP')

Cert Type (populate with 'PCC')

Forecast Submission / Inspection Date

Planned Submission / Inspection Date

Actual Submission Date

Headers for date table ('ROP MEP PCC CERT Date Range');

Date

EoW_Friday

Cumulative Measure DAX code:

Cumulative_Actual_MEP_Cert_Submissions = 

    //Cumulative actual submissions end of week date
VAR Current_Date = MAX('ROP MEP CERT Actual Date Range'[Date])
RETURN
    CALCULATE(COUNTA(ROPcertTracker[Actual Submission Date]), 'ROP MEP CERT Actual Date Range'[Date]<= Current_Date,
    USERELATIONSHIP('ROP MEP CERT Actual Date Range'[Date],ROPcertTracker[Actual Submission Date]))

Actual Submission Measure used in chart:

Actual_MEP_Cert_Submissions = 

//Actual Submissions end of week

CALCULATE(
    COUNTA(ROPcertTracker[Actual Submission Date]),
    USERELATIONSHIP('ROP MEP CERT Actual Date Range'[Date],ROPcertTracker[Actual Submission Date]))

Date table creation code:

ROP MEP PCC CERT Date Range = 
ADDCOLUMNS(
Calendar(
    IF(
        CALCULATE(
            MIN(
                MIN(
                    MINA(ROPcertTracker[Forecast Submission / Inspection  Date ]),
                    MINA(ROPcertTracker[Planned Submission / Inspection  Date])),
                MINA(ROPcertTracker[Actual Submission Date])),
                ROPcertTracker[Discipline]="MEP"
                ) = 0,
                MINA(ROPcertTracker[Planned Submission / Inspection  Date]),
        CALCULATE(
            MIN(
                MIN(
                    MINA(ROPcertTracker[Forecast Submission / Inspection  Date ]),
                    MINA(ROPcertTracker[Planned Submission / Inspection  Date])),
                MINA(ROPcertTracker[Actual Submission Date])),ROPcertTracker[Discipline]="MEP")
        ),
    CALCULATE(
        MAX(
            MAX(
                MAXA(ROPcertTracker[Forecast Submission / Inspection  Date ]),
                MAXA(ROPcertTracker[Planned Submission / Inspection  Date])),
            MAXA(ROPcertTracker[Actual Submission Date])),
        ROPcertTracker[Discipline]="MEP", 
        ROPcertTracker[Cert Type] = "PCC")
        ),
//CALCULATE(MAXA(ROPcertTracker[Date Cert Max]),ROPcertTracker[Discipline]="MEP")),
"EoW_Friday",([DATE]+7)-(WEEKDAY([Date]+7-6)))

As above, I seem to be receiving a value plotted against an erroneous '(Blank)' date as well as a correct cumulative plot.

basic error example img here:

@Amira-Bedhiafi thanks for the suggested edits. How do I implement this format when composing a question? is there a guide on here? — paulaw
You can check this guide : wikihow.com/Ask-a-Question-on-Stack-Overflow — Amira Bedhiafi

paulaw paulaw · Accepted Answer · 2019-08-08T07:56:52

This isn't a true fix, so would still welcome a proper solution, but I've found a work around that does the job for me in the interim. I have simply filtered out the '(Blank)' value from within the visual level filters for the date axis.

Why is a '(Blank)' value being returned in a date axis?

2 Answers