Forum Discussion
Fill up gaps between dates
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.
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
5 Replies
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/O653vwLTUzMI 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
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
Dear Anonymous
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.
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] ) )
