Forum Discussion

dusdau's avatar
dusdau
Icon for Helper II rankHelper II
5 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 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

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 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
      5 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

       

    • dusdau's avatar
      dusdau
      Icon for Helper II rankHelper II
      5 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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

    • tonyclifton's avatar
      tonyclifton
      Icon for Helper III rankHelper III
      3 years ago

      Dear Anonymous
      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.