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 there
In PBI, i have a data table of Month, Batch and Exit numbers setup as below.
May I know how can I have:
-a column for Closing, which sequentially minus away (from Batch 1 to 4) the Exit number from an opening number.
-a column for Opening, which takes the last calculated Closing number.
Sample output per below.
thanks!
Month | Batch | Exit | Column - Opening | Column - Closing |
Jan-18 | 1 | 100 | 1000 | 900 |
Jan-18 | 2 | 200 | 900 | 700 |
Jan-18 | 3 | 300 | 700 | 400 |
Jan-18 | 4 | 300 | 400 | 100 |
Solved! Go to Solution.
@Anonymous Please try this as a new column
ColumnClosing = VAR _Sum = CALCULATE(SUM(Test194GrpRunningTotal[Exit]),ALLEXCEPT(Test194GrpRunningTotal,Test194GrpRunningTotal[Month])) VAR _Closing = CALCULATE(SUM(Test194GrpRunningTotal[Exit]),FILTER(Test194GrpRunningTotal,Test194GrpRunningTotal[Batch]<=EARLIER(Test194GrpRunningTotal[Batch]) && Test194GrpRunningTotal[Batch]<>1)) RETURN _Sum-IF(ISBLANK(_Closing),0,_Closing)
Then add another new column for ColumnOpening as below
ColumnOpening = Test194GrpRunningTotal[ColumnClosing] + Test194GrpRunningTotal[Exit]
Proud to be a PBI Community Champion
@Anonymous Please try this as a new column
ColumnClosing = VAR _Sum = CALCULATE(SUM(Test194GrpRunningTotal[Exit]),ALLEXCEPT(Test194GrpRunningTotal,Test194GrpRunningTotal[Month])) VAR _Closing = CALCULATE(SUM(Test194GrpRunningTotal[Exit]),FILTER(Test194GrpRunningTotal,Test194GrpRunningTotal[Batch]<=EARLIER(Test194GrpRunningTotal[Batch]) && Test194GrpRunningTotal[Batch]<>1)) RETURN _Sum-IF(ISBLANK(_Closing),0,_Closing)
Then add another new column for ColumnOpening as below
ColumnOpening = Test194GrpRunningTotal[ColumnClosing] + Test194GrpRunningTotal[Exit]
Proud to be a PBI Community Champion
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 | |
101 | |
86 | |
64 |