Forum Discussion
mrothschild
3 years agoContinued Contributor
Matrix output from a Measure that uses Virtual Tables
PowerBI file is here: https://drive.google.com/file/d/15UbEraNivloq7pkqOKLUJFhnJLxFwXCi/view?usp=sharing Excel **output** file is here: https://docs.google.com/spreadsheets/d/15UptSqVU2ky4pDa8Ax7AkG...
- 3 years ago
Here is the solution:
Measure = VAR _StrtDate = EOMONTH(2023,12,1) + 1 VAR _LeaseTermTotal = // 60 _LeaseTermFirst + _LeaseTermSecond + _LeaseTermTransition VAR _EndDate = EOMONTH(_StrtDate,_LeaseTermTotal+1) VAR _CalMth = GENERATESERIES (0, DATEDIFF (_StrtDate, _EndDate, MONTH ), 1 ) VAR Virtual_Table_1.0 = ADDCOLUMNS( _CalMth, "Date", IF([Value]=0,EOMONTH(_DeploymentDate,[Value]),EOMONTH(_StrtDate,[Value]-1)+1) ) VAR Virtual_Table_1.1 = ADDCOLUMNS( Virtual_Table_1.0, "@AcqCost_1", _AcquisitionPrice_First, "@GrossCF", SWITCH(TRUE() , [Value] = 0, -1 * _AcquisitionPrice_First , [Value] = _LeaseTermFirst + 1 && _LeaseTermTotal = _LeaseTermFirst, _ExitPrice_Scenario_First , [Value] = _LeaseTermFirst + 1 && _LeaseTermTotal <> _LeaseTermFirst, _CashFlow_Gross_Second , [Value] = _LeaseTermTotal + 1, _ExitPrice_Scenario_Second , [Value] < _LeaseTermFirst + 1, _CashFlow_Gross_First , [Value] < _LeaseTermTotal + 1, _CashFlow_Gross_Second , 1234 ) ) RETURN [@GrossCF]This provides the ability to generate a Matrix of Measures out of a virtual table driven by Parameter Slicer SELECTEDVALUES().
mrothschild
3 years agoContinued Contributor
Here is the solution:
Measure =
VAR _StrtDate =
EOMONTH(2023,12,1) + 1
VAR _LeaseTermTotal =
// 60
_LeaseTermFirst + _LeaseTermSecond + _LeaseTermTransition
VAR _EndDate =
EOMONTH(_StrtDate,_LeaseTermTotal+1)
VAR _CalMth =
GENERATESERIES (0, DATEDIFF (_StrtDate, _EndDate, MONTH ), 1 )
VAR Virtual_Table_1.0 =
ADDCOLUMNS(
_CalMth,
"Date",
IF([Value]=0,EOMONTH(_DeploymentDate,[Value]),EOMONTH(_StrtDate,[Value]-1)+1)
)
VAR Virtual_Table_1.1 =
ADDCOLUMNS(
Virtual_Table_1.0,
"@AcqCost_1",
_AcquisitionPrice_First,
"@GrossCF",
SWITCH(TRUE() ,
[Value] = 0, -1 * _AcquisitionPrice_First ,
[Value] = _LeaseTermFirst + 1 && _LeaseTermTotal = _LeaseTermFirst, _ExitPrice_Scenario_First ,
[Value] = _LeaseTermFirst + 1 && _LeaseTermTotal <> _LeaseTermFirst, _CashFlow_Gross_Second ,
[Value] = _LeaseTermTotal + 1, _ExitPrice_Scenario_Second ,
[Value] < _LeaseTermFirst + 1, _CashFlow_Gross_First ,
[Value] < _LeaseTermTotal + 1, _CashFlow_Gross_Second ,
1234
)
)
RETURN
[@GrossCF]
This provides the ability to generate a Matrix of Measures out of a virtual table driven by Parameter Slicer SELECTEDVALUES().