Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi,
I have a model with 2 facts table: Plan and Actual
Four dimension tables: Date, Hour, Product, Line
I need to write a measure (not column) to show the previous value of HC, where for the same line, same product, any date, any hour, if the Plan is blank(), or Plan is 0, and Actual >0, then Previous HC = the most recent HC, where Plan >0
Like this:
here is the PBI file: link
I wrote a measure:
VAR __Filtertable = FILTER(ADDCOLUMNS(CROSSJOIN('Date', Line, 'Product', 'Hour'), "Act", [Sum of Actual], "Pl", [Sum of Plan], "HC", [Sum of HC]), [Act] + [Pl] <> BLANK()) VAR __Table1 = ADDCOLUMNS(__Filtertable, "Previous HC", VAR __currentdate = FIRSTNONBLANK('Date'[Date],1) VAR __currentline = FIRSTNONBLANK(Line[Line], 1) VAR __currentproduct = FIRSTNONBLANK('Product'[Product],1) VAR __previousdate = CALCULATE(LASTNONBLANK('Date'[Date],1), FILTER(__Filtertable, 'Date'[Date] < __currentdate && Line[Line] = __currentline && 'Product'[Product] = __currentproduct)) RETURN CALCULATE([Sum of HC], FILTER(__Filtertable, Line[Line] = __currentline && 'Product'[Product] = __currentproduct && 'Date'[Date] = __previousdate) )) RETURN SUMX(FILTER(__Table1, [Act] > 0 && [Pl] = 0), [Previous HC])
but it doesn't give the result.
please help on this.
Thanks very much
@OwenAuger : please take a look
Solved! Go to Solution.
Try this, although I believe it's overly complex due to the structure you are using (swap the ";" for "," if necessary)
Sum of Previous HC = IF ( [Sum of Actual] > 0 && ( ISBLANK ( [Sum of Plan] ) || [Sum of Plan] = 0); VAR AuxTable_ = ADDCOLUMNS ( CROSSJOIN ( DISTINCT ( Line[Line] ); CALCULATETABLE ( DISTINCT ( 'Date'[Date] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] < SELECTEDVALUE ( 'Date'[Date] ) ) ); DISTINCT ( 'Product'[Product] ); ALL ( 'Hour'[Hour] ) ); "AuxPlan_"; [Sum of Plan]; "AuxHC_"; [Sum of HC]; "SumActual_"; [Sum of Actual] ) RETURN SUMX ( AuxTable_; [AuxHC_] ) )
Hi @Iamnvt
why is the second value hand-written in blue 5 and not 10? You're not considering the current date as valid for the "most recent value"?
Try this, although I believe it's overly complex due to the structure you are using (swap the ";" for "," if necessary)
Sum of Previous HC = IF ( [Sum of Actual] > 0 && ( ISBLANK ( [Sum of Plan] ) || [Sum of Plan] = 0); VAR AuxTable_ = ADDCOLUMNS ( CROSSJOIN ( DISTINCT ( Line[Line] ); CALCULATETABLE ( DISTINCT ( 'Date'[Date] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] < SELECTEDVALUE ( 'Date'[Date] ) ) ); DISTINCT ( 'Product'[Product] ); ALL ( 'Hour'[Hour] ) ); "AuxPlan_"; [Sum of Plan]; "AuxHC_"; [Sum of HC]; "SumActual_"; [Sum of Actual] ) RETURN SUMX ( AuxTable_; [AuxHC_] ) )
@AlB this looks ok, but how can I make the Grand Total correct as sum of the row?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |