Forum Discussion

dusdau's avatar
dusdau
Icon for Helper II rankHelper II
4 years ago
Solved

Matrix - fill gaps in values between periods

Hi All,   I've been searching for a solution to this, tried some methods using M and also some DAX but I can't seem to get it to work the way I need it to.   I currently have 2 tables...a LeadTim...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi dusdau 

    If you build a relationship between your two tables, it is hard to build a measure to achieve your goal. Due to the result of measure will be impacted by your relationship. I suggest you to build a calculated table.

    Table 2 = 
VAR _T = GENERATE(VALUES('Table'[Product Name]),SUMMARIZE('Date','Date'[YearMonth],'Date'[Month Yr]))
VAR _T2 = ADDCOLUMNS(_T,"Time",CALCULATE(SUM('Table'[Lead Time(in weeks)]),'Table'[Product Name] = EARLIER([Product Name])))
VAR _T3 = ADDCOLUMNS(_T2,"MaxnotblankDate",MAXX(FILTER(_T2,[Time]<>BLANK()&&[Product Name]=EARLIER([Product Name])&&[YearMonth]<=EARLIER([YearMonth])),[YearMonth]))
VAR _T4 = ADDCOLUMNS(_T3,"LatestTime", SUMX(FILTER(_T3,[Product Name] = EARLIER([Product Name])&&[YearMonth] = EARLIER([MaxnotblankDate])),[Time]))
RETURN
_T4

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickl

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi dusdau 

If you build a relationship between your two tables, it is hard to build a measure to achieve your goal. Due to the result of measure will be impacted by your relationship. I suggest you to build a calculated table.

Table 2 = 
VAR _T = GENERATE(VALUES('Table'[Product Name]),SUMMARIZE('Date','Date'[YearMonth],'Date'[Month Yr]))
VAR _T2 = ADDCOLUMNS(_T,"Time",CALCULATE(SUM('Table'[Lead Time(in weeks)]),'Table'[Product Name] = EARLIER([Product Name])))
VAR _T3 = ADDCOLUMNS(_T2,"MaxnotblankDate",MAXX(FILTER(_T2,[Time]<>BLANK()&&[Product Name]=EARLIER([Product Name])&&[YearMonth]<=EARLIER([YearMonth])),[YearMonth]))
VAR _T4 = ADDCOLUMNS(_T3,"LatestTime", SUMX(FILTER(_T3,[Product Name] = EARLIER([Product Name])&&[YearMonth] = EARLIER([MaxnotblankDate])),[Time]))
RETURN
_T4

Result is as below.

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickl

dusdau's avatar
dusdau
Icon for Helper II rankHelper II
4 years ago

Thanks Rico!  Slightly modified and it seems to do what I needed!

 

Table 3 = 
VAR _T =
    GENERATE (
        SUMMARIZE ( 'Table', 'Table'[Plant], 'Table'[Product Name] ),
        SUMMARIZE ( 'Date', 'Date'[YearMonth], 'Date'[Month Yr] )
    )
VAR _T2 =
    ADDCOLUMNS (
        _T,
        "Time",
            CALCULATE (
                SUM ( 'Table'[Lead Time(in weeks)] ),
                'Table'[Plant] = EARLIER ( [Plant] )
                    && 'Table'[Product Name] = EARLIER ( [Product Name] )
            )
    )
VAR _T3 =
    ADDCOLUMNS (
        _T2,
        "MaxNotBlankDate",
            MAXX (
                FILTER (
                    _T2,
                    [Time] <> BLANK ()
                        && [Plant] = EARLIER ( [Plant] )
                        && [Product Name] = EARLIER ( [Product Name] )
                        && [YearMonth] <= EARLIER ( [YearMonth] )
                ),
                [YearMonth]
            )
    )
VAR _T4 =
    ADDCOLUMNS (
        _T3,
        "LatestTime",
            SUMX (
                FILTER (
                    _T3,
                    [Plant] = EARLIER ( [Plant] )
                        && [Product Name] = EARLIER ( [Product Name] )
                        && [YearMonth] = EARLIER ( [MaxNotBlankDate] )
                ),
                [Time]
            )
    )
RETURN
    _T4