Matrix - fill gaps in values between periods

[dusdau]

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 LeadTimeTracking table and a dynamic Calendar table, based on the earliest LeadTimeTracking date up until today.

LeadTimeTracking table and sample data:

Created (Date)

Plant (Text)

Product (Text)

Lead Time (integer)

I want to show the Lead Time data in a matrix and have the previously input lead time show until the next month when the lead time is entered.  I'd like it to display as follows, with all the gaps filled in by the previously input lead time value, if one exists.

I've explored left natural joins and cross joins between the Calendar and LeadTimeTracking tables as well as other DAX measures but have yet to find a workable solution. 

Can anyone point me in the right direction?  I'd love to be able to do this with just a DAX measure if possible, but would entertain anything that works at this point!

Thanks,

Dustin

[v-rzhou-msft · Accepted solution]

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

[tonyclifton]

Dear @v-rzhou-msft
during my search for a solution for my problem I found your answer to be very similar from what I want to achieve. The difference is that each "product name" only has one Created date and the product dates allways follow after each other.

Example:

Now instead of adding up the values over time I would need to get the value of the product at the created date.

Example:

Maybe you have an idea how this can be achieved.
Thanks in advance for your time.

[dusdau]

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

[dusdau]

Thanks Rico, will give this a try and report back!

[v-rzhou-msft]

Hi @dusdau 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share a sample file without sensitive data with me.

Best Regards,

Rico Zhou

[dusdau]

Rico, I think your example will work but I need to include Plant in Table 2 also so was going to try to figure that out unless you can tweak the GENERATE statement quickly for me?

Sorry my DAX is fairly beginner still....