How to apply ORDER BY inside UNION

[nikhil425]

Hello DAX gurus, 

Can you please help!

I am trying to order the data from one of the tables inside UNION. Below is my DAX query

 EVALUATE
VAR UNIONTable =
    UNION (
        SELECTCOLUMNS (
            CALCULATETABLE (
                'Funds_PI - Operations',
                FILTER (
                    ALL ( 'Funds_PI - Operations' ),
                    'Funds_PI - Operations'[Operation Date] >= DATEVALUE ( "4/6/2016" )
                ),
                FILTER (
                    ALL ( 'Funds_PI - Operations' ),
                    'Funds_PI - Operations'[Operation Date] <= DATEVALUE ( "5/31/2022" )
                ),
                FILTER (
                    ALL ( 'Funds_PI - Operations' ),
                    'Funds_PI - Operations'[Operation Type] <> "Valuation"
                        && 'Funds_PI - Operations'[Operation Type] <> "Cash-Adjusted Valuation"
                        && 'Funds_PI - Operations'[Operation Type] <> "Commitment"
                ),
                FILTER (
                    ALL ( 'Funds_PI - Operations' ),
                    'Funds_PI - Operations'[fundkey] = 312
                )
            ),
            "fundkey", 'Funds_PI - Operations'[fundkey],
            "OperationDate", 'Funds_PI - Operations'[Operation Date],
            "SignedCash", 'Funds_PI - Operations'[IRR Cashflow (USD)],
            "OperationType", 'Funds_PI - Operations'[Operation Type]
        ),
        /*Getting the end valuation from the cashflows*/
        SELECTCOLUMNS (
            CALCULATETABLE (
                'Funds_PI - Operations',
                FILTER (
                    ALL ( 'Funds_PI - Operations' ),
                    'Funds_PI - Operations'[Operation Type] = "Valuation"
                        || 'Funds_PI - Operations'[Operation Type] = "Cash-Adjusted Valuation"
                ),
                FILTER (
                    ALL ( 'Funds_PI - Operations' ),
                    'Funds_PI - Operations'[Operation Date] = DATEVALUE ( "5/31/2022" )
                ),
                FILTER (
                    ALL ( 'Funds_PI - Operations' ),
                    'Funds_PI - Operations'[fundkey] = 312
                )
            ),
            "fundkey", 'Funds_PI - Operations'[fundkey],
            "OperationDate", 'Funds_PI - Operations'[Operation Date],
            "SignedCash", 'Funds_PI - Operations'[IRR Cashflow (USD)],
            "OperationType", 'Funds_PI - Operations'[Operation Type]
        )
    ) --ORDER BY [OperationDate]
RETURN
    XIRR ( UNIONTABLE, [SignedCash], [OperationDate], 0.8 )

Below is the actual output from the Union Table, I can apply ORDER BY for uniontable, however when using the XIRR it doesn't allow me to ORDERBY. 

Fundkey	OperationDate	SignedCash	OperationType
312	11/22/2021 12:00:00 AM	174509804	Distribution
312	6/8/2018 12:00:00 AM	24377447	Distribution
312	9/5/2018 12:00:00 AM	0	Distribution
312	9/5/2018 12:00:00 AM	52177305	Distribution
312	11/30/2018 12:00:00 AM	23641956	Distribution
312	3/27/2019 12:00:00 AM	12428976	Distribution
312	6/18/2019 12:00:00 AM	14049019	Distribution
312	9/10/2019 12:00:00 AM	19100403	Distribution
312	11/27/2019 12:00:00 AM	11123037	Distribution
312	3/23/2020 12:00:00 AM	36219023	Distribution
312	6/4/2020 12:00:00 AM	37247067	Distribution
312	12/10/2020 12:00:00 AM	36788955	Distribution
312	9/30/2021 12:00:00 AM	66666667	Distribution
312	8/31/2018 12:00:00 AM	0	Call
312	11/30/2019 12:00:00 AM	0	Call
312	4/6/2016 12:00:00 AM	-2941176	Call
312	4/19/2016 12:00:00 AM	-4411765	Call
312	5/2/2016 12:00:00 AM	-3431373	Call
312	6/21/2016 12:00:00 AM	3431373	Call
312	4/12/2017 12:00:00 AM	-15196076	Call
312	5/2/2017 12:00:00 AM	-50882353	Call
312	10/5/2017 12:00:00 AM	-23529412	Call
312	11/16/2017 12:00:00 AM	-103039218	Call
312	6/8/2018 12:00:00 AM	-24377447	Call
312	9/5/2018 12:00:00 AM	-50216520	Call
312	11/30/2018 12:00:00 AM	-23641956	Call
312	3/27/2019 12:00:00 AM	-12428976	Call
312	6/18/2019 12:00:00 AM	-14049019	Call
312	9/10/2019 12:00:00 AM	-19100403	Call
312	11/27/2019 12:00:00 AM	-11123037	Call
312	3/23/2020 12:00:00 AM	-30728828	Call
312	6/4/2020 12:00:00 AM	-19517477	Call
312	5/31/2022 12:00:00 AM	-3254186	Cash-Adjusted Valuation

I want to make the cashflows in Ascending order, but the XIRR function is not liking the ORDER BY.

any help greatly appreciated, Thank you

[OwenAuger]

Hi @nikhil425 

ORDER BY can only be used follwing the final table expression returned by an EVALUATE statement

i.e. EVALUATE <table> ORDER BY...

Given that your expression returns the scalar result of XIRR, presumably it is intended to be a measure - is that correct? It's not possible to use ORDER BY within a measure definition.

In any case, there is no ability (or need) to enforce the ordering of rows for a table provided as the first argument of XIRR.

XIRR can handle multiple occurrences of dates, though grouping the table by date before passing to XIRR may improve performance. XIRR will also treat the minimum date expression evaluated by the 3rd argument as the "first" date for the purpose of the calculation.

I note that calling XIRR using your particular sample table produces a result of 10.7% if a guess argument <= 0.57 is provided.

Regards,

Owen

[nikhil425]

Thank you  @OwenAuger for your reply. 

The reason I wanted to order the cashflows is because sometime the order show 0 cashflow as the very first cashflow and XIRR doesn't calculate if the first cashflow is 0. But if I order them ascending then my first cashflow will always have a number. 

In my example above, I can apply the ORDER BY in the Var UNION Table above, which I commented out. 

I get an error when I use the Temptable in XIRR function that has an ORDER BY. I am assuming may there are some other ways to create a table with pre-order setup. 

[OwenAuger · Accepted solution]

Thanks for clarifying the issue @nikhil425 

For the purpose of XIRR, the "ordering" of cashflows is solely determined by the date expression provided to XIRR, in your example [OperationDate].

The "first cashflow" will be the cashflow corresponding to the minimum value of [OperationDate].

If you need to control the content of UNIONTable, you could filter or apply other operations to ensure that the cashflow on the earliest date meets certain criteria, or to control what the earliest date is.

ORDER BY can't be used in this situation (i.e. with a table variable).

Apart from DAX queries (EVALUATE ... ORDER BY ... ) there is no way to control the ordering of rows of a table within DAX.

Regards,

Owen