Cumulative Repair Rates

[Anonymous]

I have reviewed every post I can find on the topic, however, I can't find one that matches my criteria. What I want to do is chart a product repair rate per month and cumulative overtime. I've tried to create a measure and a separate table that does this however, I cannot get either to work. I've included screen caps for what I want and included a link for Excel Sample Data. 

What I want

What I get

DAX Measure

Cumulative Repair Rate = 
VAR Cumulative_Repairs = 
    COUNTROWS(
        FILTER('Project-1 Log',
            FIND("R",'Project-1 Log'[Suffix],,0
            ) > 0
        )
    )+
    CALCULATE(
        SUM('Project-2 Log'[Repairs]),
            FILTER(
                ALLSELECTED('Project-2 Log'),
                'Project-2 Log'[Month] <= MAX('Project-2 Log'[Month])
        )
    )

VAR Cumulative_Welds = 
    CALCULATE(
        DISTINCTCOUNT('Project-1 Log'[IDNo]),
        FILTER(
            ALLSELECTED('Project-1 Log'),
            'Project-1 Log'[DateCompleted] <= MAX('Project-1 Log'[DateCompleted])
        )
    )+
    CALCULATE(
        SUM('Project-2 Log'[QtyID]),
        FILTER(
            ALLSELECTED('Project-2 Log'),
            'Project-2 Log'[Month] <= MAX('Project-2 Log'[Month])
        )
    )
RETURN

DIVIDE(Cumulative_Repairs,Cumulative_Welds,0)

Model Mockup

(the actual model is quite large so I extracted the relevant parts that affect the measure, both are Many-to-1 relationships)

 

[AlexisOlson · Accepted solution]

I think what's going on is that you're using 'Project-2 Log'[Month] as the chart's x-axis but this isn't related to 'Project-1 Log'[DateCompleted]. This means that the first component of your Cumulative_Welds variable is just a constant value DISTINCTCOUNT ( 'Project-1 Log'[IDNo] ).

The best solution is to create a date dimension table with relationships to both project tables and use that for your chart axis and inside your measure instead of the date columns of the project table.

Cumulative Repair Rate =
VAR CurrDate =  MAX ( dimDate[Date] )
VAR Cumulative_Repairs =
    COUNTROWS (
        FILTER (
            'Project-1 Log',
            FIND ( "R", 'Project-1 Log'[Suffix],, 0 ) > 0
        )
    ) +
    CALCULATE (
         SUM ( 'Project-2 Log'[Repairs] ),
         dimDate[Date] <= CurrDate
    )
VAR Cumulative_Welds =
    CALCULATE (
        DISTINCTCOUNT ( 'Project-1 Log'[IDNo] ),
        dimDate[Date] <= CurrDate
    ) +
    CALCULATE (
        SUM ( 'Project-2 Log'[QtyID] ),
        dimDate[Date] <= CurrDate
    )
RETURN
    DIVIDE ( Cumulative_Repairs, Cumulative_Welds, 0 )