For this DAX, I can select individual Items one-by-one and it will populate an actual rate. The issue is a select all situation. If I wish to see all locations Actual Rates, The query exceeds resources, and the visual breaks.
Is there any way I can simplify or optimize this formula? Am I using too many variables?
VAR maxinventorydate =
CALCULATE(
MAX(fct_fsr_inputs[ReportStartDate]),
REMOVEFILTERS('Date')
)
VAR beginventorydate =
CALCULATE(
MAX(fct_fsr_inputs[ReportStartDate]),
FILTER(ALL('Date'), 'Date'[Date] < maxinventorydate)
)
VAR gallonsdelivered =
VAR gallons =
CALCULATE(
SUM(fct_invoice_lines[Gallons]),
FILTER(ALL('Date'), 'Date'[Date] < maxinventorydate && 'Date'[Date] > beginventorydate)
)
VAR stgallons =
CALCULATE(
SUM(fct_st_invoices[Quantity]),
FILTER(ALL('Date'), 'Date'[Date] < maxinventorydate && 'Date'[Date] > beginventorydate)
)
RETURN IF(ISBLANK(gallons),stgallons,gallons)
VAR endinginventory =
CALCULATE(MAX(fct_fsr_inputs[Tank Inventory]),
FILTER(ALL('Date'), 'Date'[Date] = maxinventorydate)
)
VAR beginninginv =
CALCULATE(MAX(fct_fsr_inputs[Tank Inventory]),
FILTER(ALL('Date'), 'Date'[Date] = beginventorydate)
)
VAR days = DATEDIFF(beginventorydate,maxinventorydate,DAY)
VAR useage = (beginninginv + gallonsdelivered) - endinginventory
VAR actualrate = useage/days
RETURN
actualrate/4 ```
daysand see if you get the same issue. Also is this something you can move to a Calculated Column?