Running total for specific period

[didijaba]

HI, I have formula that doesn't start calculating running total from first date of selected period but from earliest in date column.
here is code so far

RunningTotalTotalExpensesTEST = 
CALCULATE(SUM(tblOfferSaleData[TotalExpenses]),
FILTER(ALLSELECTED(tblOfferSaleData[SalesDateCalc]),
MIN('Calendar'[Date])<=tblOfferSaleData[SalesDateCalc] &&
 MAX('Calendar'[Date])>=tblOfferSaleData[SalesDateCalc]))

And this one looks properly, but doesn't adjust to date slicer

RunningTotalTotalExpenses = 
CALCULATE(SUM(tblOfferSaleData[TotalExpenses]),
FILTER(ALLSELECTED(tblOfferSaleData),
tblOfferSaleData[SalesDateCalc]<=MAX(tblOfferSaleData[SalesDateCalc])))

 

[tallan]

Didijaba,

Using a calendar table that had no relationship with tblOfferSaleData, the following formula worked:

RunningTotalTotalExpensesTEST:=CALCULATE (
SUM ( tblOfferSaleData[Total Expenses] ),
FILTER (
tblOfferSaleData,
MIN ( 'Calendar'[Date] ) <= tblOfferSaleData[SalesDateCalc]
&& MAX ( 'Calendar'[Date] ) >= tblOfferSaleData[SalesDateCalc]
)
)

If you would like to learn more about disconnected tables in a DAX data model, please see chapters 12 and 13 in Power Pivot and Power BI by Rob Collie and Avi Singh.

Tom Allan
PowerPivotPro - Transforming your Business with Power Pivot and Power BI

 

[akice]

It doesn't start from first date of selected period because of the 'ALLSELECTED(tblOfferSaleData[SalesDateCalc])'. ALLSELECTED removes the top most filter applied to the 'tblOfferSaleData[SalesDateCalc]' column, which in your case may result in all the values in the table being returned. So you may want to remove the ALLSELECTED() and just go with FILTER ( VALUES ( tblOfferSaleData[SalesDateCalc] ), ... ) or FILTER ( tblOfferSaleData, ... ).