Formula execution results in Excel crash while DAX Studio works fine

[razmochaev]

Hi everyone.

I ran into the problem and I think it's because of imperfect formula.

I need to compute costs attributable to projects only for projects that had sales in a given period. Also, the costs should be accumulated between period of current sale and previos sale.

The data model is in Russian, so I give the translation:

Проекты = Projects

Календарь[Дата] = Calendar[Date]

Продажи = Sales

[Выручка] = [SalesAmount]

Себестоимость = Cost Of Services Rendered

The DAX code I've come up with:

EVALUATE
VAR ProjectsSalesDates =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZECOLUMNS ( 'Проекты'[id], 'Календарь'[Дата] ),
            "Продажи", [Выручка]
        ),
        NOT ( ISBLANK ( 'Проекты'[id] ) )
            && [Продажи] > 0
    )
VAR ProjSalesPrevDates =
    ADDCOLUMNS (
        ProjectsSalesDates,
        "PrevSalesDate", MAXX (
            (
                VAR CurrProject = [id]
                VAR CurrDate = [Дата]
                VAR ProjectDates =
                    FILTER ( ProjectsSalesDates, [id] = CurrProject && [Дата] < CurrDate )
                RETURN
                    ProjectDates
            ),
            [Дата]
        )
    )
VAR ProjectsCosts =
    ADDCOLUMNS (
        ProjSalesPrevDates,
        "Себестоимость трудозатрат", CALCULATE (
            [2.2. Стоимость трудозатрат],
            (
                VAR CurrDate = [Дата]
                VAR PrevDate = [PrevSalesDate]
                VAR CostPeriod =
                    DATESBETWEEN ( 'Календарь'[Дата], PrevDate, CurrDate )
                RETURN
                    CostPeriod
            )
        ),
        "Себестоимость прямых затрат", CALCULATE (
            [Прямые затраты],
            (
                VAR CurrDate = [Дата]
                VAR PrevDate = [PrevSalesDate]
                VAR CostPeriod =
                    DATESBETWEEN ( 'Календарь'[Дата], PrevDate, CurrDate )
                RETURN
                    CostPeriod
            )
        )
    )
VAR Result =
    ADDCOLUMNS (
        ProjectsCosts,
        "Себестоимость", [Себестоимость трудозатрат] + [Себестоимость прямых затрат]
    )
RETURN
    SUMX ( Result, [Себестоимость]) 

 In DAX Studio is works fine and returns the following table:

It returns 529 rows and execution time is 3250 ms.

But when I input this formula in Excel, it crashes :-(.

If anyone could give me a hint, whether it's due to bad formula of something else, I'd be very grateful!

[razmochaev]

I also tested it in PowerBI and it works pretty well, but in different versions of Excel it results in a crash.

[Anonymous]

First off, you should never use SUMMARIZECOLUMNS in a measure since this function does not respect context transition. Then, the measure (is it a measure or just a query?) seems to me to be a bit too complex with calculations that are materialized, probably unnecessary, thus making the calculations slower than they could be.

Secondly, what's the exact error message you get?

Best
D

[razmochaev]

Thank you for the remarks! I've made some improvements in the formula and now it doesn't crash. Still, I didn't get the result I wanted, so I guess this topic must be closed.