The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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!
I also tested it in PowerBI and it works pretty well, but in different versions of Excel it results in a crash.
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.
User | Count |
---|---|
25 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
31 | |
11 | |
10 | |
10 | |
9 |