GENERATESERIES with SELECTEDVALUE

[mrothschild]

PBIX sample file is here: https://drive.google.com/file/d/13w7kMB_WKJABXnezPOrQhqlOFUXP4Wcl/view?usp=sharing

I'm trying to allow a user to effectively create a calculated table of a start point and end point between two months using GENERATESERIES.  In the screenshot below, I can input the "Start Month" and "Duration" and that's working fine.  

When I create a calculated table (or parameter) using the following I get an error "The arguments in GenerateSeries function cannot be blank"

Any way to work around/solve this?  Thanks!

Parameter = 

GENERATESERIES(
    _Measures[Measure - Start Month]
    , 60
    , 1)

[mrothschild · Accepted solution]

@v-cgao-msft Thank you.

The following is as much for me as it is for others who come across this feed.  PowerBI cannot create a new Calculated Table using input from Slicers.  It can, however, create a Virtual Calculated Table in a Measure that can get to the results you want, but is difficult to audit.  Accordingly, I recommend that you create a Calculated Table with "hardcoded" inputs to audit/program/build, while you're replicating the Measure output you seek using the virtual table for dynamic purposes.

This is my auditing Calculated Table.  You'll note that the RETURN has to be a Table.

zzNewTable = 

VAR StrtDate =
    DATE ( 2023, 6, 1 )

VAR EndDate = 
    EOMONTH(StrtDate,60+1)

VAR CalMth =

    GENERATESERIES (
        0,
        DATEDIFF (
            StrtDate,
            EndDate,
            MONTH
        ),
        1
    )


// NetRent = AcquisitionCost * LRF * (1-TechServicingFee)
VAR NetRent =   
         10000 * 0.015 * (1-0.05)

// Deployment = AcquisitionCost - LeaseTransactionCosts
VAR Deployment =   
         10000 + 50


// Exit = AcquisitionCost * RV% - LeaseTransitionCosts
VAR Exit =   
         10000 * 0.75 - 50


VAR T1_1 = 
    ADDCOLUMNS(
        CalMth,
        "Date",
            IF([Value]=0,EOMONTH(StrtDate,[Value]),EOMONTH(StrtDate,[Value]-1)+1),
        "@AcqCost",
        10000,        
        "@LRF",
        0.015,
        "@RV%",
        0.75,
        "@LeaseTerm",
        60,
        "@NetRent",
            IF([Value]=0,(-1)*Deployment,IF([Value]=61,Exit,NetRent)) ,
        
        
        "@StartMonth",
        6
    )

VAR UnlevIRR = 
    XIRR(T1_1,[@NetRent],[Date])
    

RETURN
T1_1

This is my Measure.  You'll note that the RETURN needs to be a Measure (and not a Table)

zzVirtual Table Measure = 

VAR StrtDate =
    DATE ( [Measure - Start Year], [Measure - Start Month], 1 )

VAR EndDate = 
    EOMONTH(StrtDate,[Measure - Lease Term]+1)

VAR CalMth =
    GENERATESERIES (0, DATEDIFF (StrtDate, EndDate, MONTH ), 1  )

// NetRent = AcquisitionCost * LRF * (1-TechServicingFee)
VAR NetRent =   
//         10000 * 0.015 * (1-0.05)
    [Measure - Acquisition Price] * [Measure - Lease Rate Factor] * (1-[Measure - Technical Servicing Fee])



// Deployment = AcquisitionCost - LeaseTransactionCosts
VAR Deployment =   
//        10000 + 50
        [Measure - Acquisition Price] + [Measure - Lease Transaction Costs]

// Exit = AcquisitionCost * RV% - LeaseTransitionCosts
VAR Exit =   
//        10000 * 0.75 - 50
        [Measure - Acquisition Price] * [Measure - Residual Percentage] - [Measure - Lease Transaction Costs]

VAR T1_1 = 
    ADDCOLUMNS(
        CalMth,
        "Date",
            IF([Value]=0,EOMONTH(StrtDate,[Value]),EOMONTH(StrtDate,[Value]-1)+1),
        "@AcqCost",
        10000,        
        "@LRF",
        0.015,
        "@RV%",
        0.75,
        "@LeaseTerm",
        60,
        "@NetRent",
            IF([Value]=0,(-1)*Deployment,IF([Value]=[Measure - Lease Term]+1,Exit,NetRent)) ,
        
        
        "@StartMonth",
        6
    )

VAR UnlevIRR = 
    XIRR(T1_1,[@NetRent],[Date])
       
RETURN
UnlevIRR

[v-cgao-msft]

Hi @mrothschild ,

The first parameter [Measure - Start Month] is blank at this time, please try this measure.

Measure - Start Month = 
 
SELECTEDVALUE('Input - Lease Start Month'[Lease Start Month],MIN('Input - Lease Start Month'[Lease Start Month]))

SELECTEDVALUE

Measure can be referenced in a calculation table/column, but in this case the calculation table/column forces the calculation of the measure in the currently defined context. This operation translates and integrates the result of the measure into a column, which is not affected by the user's actions (e.g. change the slicer).

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[mrothschild]

@v-cgao-msft 

Thanks for the feedback.  Unfortunately, that doesn't solve the issue.  My intended outcome in this example is to create a calculated table that starts at the user-selected number in the [Measure].  So in the above situation, the table would consist of a column of whole numbers from 2 - 60.  If I changed "Lease Start Month" to 6, it would change from 6  - 60.

Here's the output of what your suggestion provided:

[v-cgao-msft]

Hi @mrothschild ,

//Measure can be referenced in a calculation table/column, but in this case the calculation table/column forces the calculation of the measure in the currently defined context. This operation translates and integrates the result of the measure into a column, which is not affected by the user's actions (e.g. change the slicer). 

In import mode, calculated columns/tables are stored in memory and they are recalculated only when the table/memory is refreshed, and the user changes the behavior of the slicer does not affect the calculated columns/tables.

I assume you want to create a dynamic table based on the slicer's selected values, but that's not possible. 

You can vote for similar idea.

Best Regards,
Gao
Community Support Team

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum