Forum Discussion
Calculating Last Week Total Unreimbursed Amount Using CALCULATE and DATEADD
Hi all,
I have calculated the Unreimbursed Amount for a cummulative dataset, however, I want to calculate the same Unreimbursed Amount Last Week (or some given number of days earlier e.g. 7 or 8 days ago).
I came up with this formula, but I am not getting the correct answer.
Last Week URMB =CALCULATE(
[PPNL RMB Amount],
DATEADD('Calendar'[Date],-7,DAY)
)
This is not producing the answer.
Need help
5 Replies
DATEADD function returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.
Your formula should work fine if you pull that measure in a visual that has the Calendar[Date] field as well.
But if you are looking for an aggregation, you would need to use a different formula that filters (instead of time shifting the current context) records for the desired dates.
I Guess, the DATEADD is the filter in the CALCULATE fuction producing a date table representing all dates to lastdate -7 days. Then CALCULATE only sum up unreimbursed amounts within these dates.
- Try to add the all(date table) filter to your calculate function. Also you might want to look at datesinbetween function instead of dateadd
