cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

Created (Date)

Plant (Text)

Product (Text)

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

1 ACCEPTED SOLUTION
Community Support

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 _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 6
Community Support

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 _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

Helper III

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.

Helper II

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 =
_T,
"Time",
CALCULATE (
SUM ( 'Table'[Lead Time(in weeks)] ),
'Table'[Plant] = EARLIER ( [Plant] )
&& 'Table'[Product Name] = EARLIER ( [Product Name] )
)
)
VAR _T3 =
_T2,
"MaxNotBlankDate",
MAXX (
FILTER (
_T2,
[Time] <> BLANK ()
&& [Plant] = EARLIER ( [Plant] )
&& [Product Name] = EARLIER ( [Product Name] )
&& [YearMonth] <= EARLIER ( [YearMonth] )
),
[YearMonth]
)
)
VAR _T4 =
_T3,
"LatestTime",
SUMX (
FILTER (
_T3,
[Plant] = EARLIER ( [Plant] )
&& [Product Name] = EARLIER ( [Product Name] )
&& [YearMonth] = EARLIER ( [MaxNotBlankDate] )
),
[Time]
)
)
RETURN
_T4

Helper II

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

Community Support

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

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.