cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hashiru Regular Visitor
Regular Visitor

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 5
tarunsingla Established Member
Established Member

Re: Calculating Last Week Total Unreimbursed Amount Using CALCULATE and DATEADD

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.

 

Hashiru Regular Visitor
Regular Visitor

Re: Calculating Last Week Total Unreimbursed Amount Using CALCULATE and DATEADD

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.

Nishantjain Member
Member

Re: Calculating Last Week Total Unreimbursed Amount Using CALCULATE and DATEADD

Try to add the all(date table) filter to your calculate function. Also you might want to look at datesinbetween function instead of dateadd
Hashiru Regular Visitor
Regular Visitor

Re: Calculating Last Week Total Unreimbursed Amount Using CALCULATE and DATEADD

Thanks Nishhantjain/Tarunsingla,

 

Tarunsingla: I will like to accomplish this with DATEADD as well as other available functions.

 

Nishantjain: I tried the DATESBETWEEN and I am getting some answers that not accurate but I have a feeling it the slicers not tuned well. I am working with Excel Power Pivot also with this and it does filtered well in Excel because I can link them easily there. However, I am not that clear how to adjust the slicers in Power BI for the report to be filtered by both measures correctly.

 

Here is a background on the model I am building:

 

Relationship: Fact[TransDate] * to 1 Calendar[Date]

Slicer: FAIN and Activity

 

Dataset: Cumulative since 2016

 

 

Highlighted
Hashiru Regular Visitor
Regular Visitor

Re: Calculating Last Week Total Unreimbursed Amount Using CALCULATE and DATEADD

Here is the  formula I am using:

 

LW RMB Amount = CALCULATE([NL RMB Amount],DATESBETWEEN(Calendar[Date],FIRSTDATE(Fact[Accounting]),DATE(2019,3,25)))

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 204 members 2,727 guests
Please welcome our newest community members: