Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
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 @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.
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] )
)
@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
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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
104 | |
78 | |
71 |