How to Calculate cumulative count from one table based on months in another table

[WTAS80486]

Measure is:

COUNT() = COUNTX(FILTER(Table 1, Table 1[PM/CM] = "Corrective"

&& Table 1[Month Number(Failed Date)] <> BLANK()

&& Table 1[Month Name(Failed Date)] =MAX('Table 2[Month Name])),  Table 1[PM/CM])

‘Table 1’  filtered by asset no:

It has dec, Jan and June entries for this asset

It also has this column Table1[PM/CM]

Table 2 looks like this and has nov-jul entries

Now cumulative count has to be calculated Nov to Jul

So for Jan count should be 3 and march also 3.

Can anyone help!

[SpartaBI · Accepted solution]

@WTAS80486 make sure you have the proper dimensions between the different facts and don't created a relationship between the facts themselves.
And create these measures:

COUNT(failures_month) V2 = 
COUNTROWS(
    FILTER(
        Workorder_with_assetdowntime_MTBF, 
        Workorder_with_assetdowntime_MTBF[PM/CM] = "Corrective" 
            && Workorder_with_assetdowntime_MTBF[Month Number(Failed Date)] <> BLANK()
    )
)

COUNT(failures_month) V2 Cummulative = 
VAR _max_date = max('Date'[Date])
VAR _max_peossible_date = MAX(Workorder_with_assetdowntime_MTBF[Failed Date New])
RETURN
CALCULATE(
    [COUNT(failures_month) V2], 
    'Date'[Date] <= _max_date && 'Date'[Date] <= _max_peossible_date
)

[WTAS80486]

@SpartaBI Its working! Thanks so much!

[SpartaBI · Accepted solution]

@WTAS80486 make sure you have the proper dimensions between the different facts and don't created a relationship between the facts themselves.
And create these measures:

COUNT(failures_month) V2 = 
COUNTROWS(
    FILTER(
        Workorder_with_assetdowntime_MTBF, 
        Workorder_with_assetdowntime_MTBF[PM/CM] = "Corrective" 
            && Workorder_with_assetdowntime_MTBF[Month Number(Failed Date)] <> BLANK()
    )
)

COUNT(failures_month) V2 Cummulative = 
VAR _max_date = max('Date'[Date])
VAR _max_peossible_date = MAX(Workorder_with_assetdowntime_MTBF[Failed Date New])
RETURN
CALCULATE(
    [COUNT(failures_month) V2], 
    'Date'[Date] <= _max_date && 'Date'[Date] <= _max_peossible_date
)

[WTAS80486]

Thanks @SpartaBI for the solution. You are so helpful!

[WTAS80486]

@SpartaBI 

This measure:

COUNT(failures_month) V2 Cummulative =

VAR _max_date = max('dimDate'[Date])

VAR _max_peossible_date = calculate(max(MAX(Workorder_with_assetdowntime_MTBF[Date(Failed)]),MAX('Runhours_Total Hours_By Month'[Date(Start)])), REMOVEFILTERS(dimDate),  Workorder_with_assetdowntime_MTBF[PM/CM] = "Corrective"

            ,Workorder_with_assetdowntime_MTBF[Month Name(Failed Date)] <> BLANK())

RETURN

IF(

    _max_date <= _max_peossible_date,

    CALCULATE(

        [COUNT(failures_month) V2],

    'dimDate'[Date] <= _max_date

    )+0

)

Is not populating cumulative count for last month, JULY in this case, This last month is Date(start) from runhours_totalhours table. 

Date(failed) from workorder_with_asset downtime table

[SpartaBI · Accepted solution]

Change this part:

IF(

    _max_date <= _max_peossible_date,

To this

IF(

EOMONTH(  _max_date, -1) <= _max_peossible_date,