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 LeadTim...
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

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

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.