Forum Discussion

mrothschild's avatar
mrothschild
Continued Contributor
3 years ago
Solved

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...
  • mrothschild's avatar
    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().