Fill up gaps between dates

[tonyclifton]

Dear community,

I'm refering to this post: Matrix - fill gaps in values between periods which is pretty much what I need as an end result but with the difference that in my application each "product name" only has one Created date and the product dates always follow after each other.

Example:

AND instead of adding up the values ("Lead Time") I need to show the value of the respective product from it's start date until the next products' start date.

Example of desired result for the total row:

Thank you  in advance.

[v-yangliu-msft · Accepted solution]

Hi  @tonyclifton ,

Here are the steps you can follow：

1. Create calculated column.

Product1 = 
MINX(
    FILTER(ALL('Table 2'),
    'Table 2'[YearMonth]=EARLIER('Table 2'[YearMonth])&&'Table 2'[Time]<>BLANK()),[Product Name])

Product2 =
IF(
    [Product1]=BLANK(),
    MAXX(
        FILTER(ALL('Table 2'),
        'Table 2'[YearMonth]<=EARLIER('Table 2'[YearMonth])),[Product1]),[Product1])

2. Create measure.

Measure =
IF(
    HASONEVALUE('Table 2'[Product Name]),
    MAX('Table 2'[LatestTime]),
    MAXX(
        FILTER(ALL('Table 2'),

        'Table 2'[Product2]=MAX('Table 2'[Product2])),[Time]))

3. Result:

Best Regards,

Liu Yang

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

[tonyclifton]

Dear @v-yangliu-msft 
thank you very much for the example - it looks really good. However I forgot one condition.
I need the calculation to be based on the Plant that is selected. I haven't figured out where to adjust the dax for that. Maybe you can help me out here again.

I adjusted the example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI3MAQiJR2lgJzEvJIIIMMRiE2AGCJrCpKN1YlG5iIpdgJiU5hiQyNUxSZIiiOhJpvDTTZDVWyGphhksiVEsTHMGbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, Plant = _t, #"Product Name" = _t, #"Lead Time(in weeks)" = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date},{"EndDate", type date}, {"Plant", type text}, {"Product Name", type text}, {"Lead Time(in weeks)", Int64.Type}})
in
    #"Changed Type"

Thank you.

[tonyclifton]

I manged to adjust it myself:

Product1 = MINX( FILTER(ALL('Table 2'), 'Table 2'[Plant] = EARLIER('Table 2'[Plant]) && 'Table 2'[YearMonth]=EARLIER('Table 2'[YearMonth])&& 'Table 2'[Time]<>BLANK()), [Product Name])

Product2 = 
IF(
    [Product1]=BLANK(),
    MAXX(
        FILTER(ALL('Table 2'),
        'Table 2'[Plant] = EARLIER('Table 2'[Plant]) &&
        'Table 2'[YearMonth]<=EARLIER('Table 2'[YearMonth]))
        ,[Product1]),[Product1])

Measure = 

CALCULATE (
	Max('Table 2'[LatestTime]), 
	FILTER(ALLSELECTED('Table'[Plant] ), 'Table'[Plant] = 'Table'[Plant] )
)

[amitchandak]

@tonyclifton , If you do have a end date , add a column

End Date = Today()

Then follow the code

Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs

or

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

[tonyclifton]

I managed to add an End date based on the succeeding products' start date.
But I couldn't get the rest to work with my existing DAX.
Maybe you can have a look.
I created a Sample File