Calculated Measure/Column to show difference in value from last 7 days , last month, and last year

[clarkbj]

I have been trying to create a calculated measure or column to show the difference in values from current day to last week (7 days), last month, and last year to see if it in increasing or decreasing and by how much but can't figure out what I need to put in my formula to do this.  The data is a daily count. 

  

[pxg08680]

@clarkbj

Can you give some example to calculate difference using the data in your image..

[clarkbj]

I need to create some cards with multiple metrics including the one I showed (Current Tag Count) and the difference from 7 days ago, 1 month ago (30 days), etc.  Here is what the data should show.  The Card below it is an example ... though I am guessing I will have to create seperate cards to show the WTD Difference and MTD Difference.

[Ashish_Mathur]

Hi,

In the slicer, select any date.  Now try these measures:

=CALCULATE(SUM(Data[Current_tag_count]),FILTER(Data,MAX(Data[Report_Datetime])-7)

=CALCULATE(SUM(Data[Current_tag_count]),FILTER(Data,MAX(Data[Report_Datetime])-30)

=CALCULATE(SUM(Data[Current_tag_count]),FILTER(Data,MAX(Data[Report_Datetime])-365)

Hope thsi helps.

[clarkbj]

I added these measures and included a date slicer but it is showing the same number for all three measures which is the Current Tag Count for the date I select. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Also, show the correct result there.

[clarkbj · Accepted solution]

Not sure where I can place this file that you would be able to link into?  Is there a standard place that people typically use?  I think I figured out what I need to do.

Create a date slicer and select any individual date

Create new columns:

Current_Tag_Count_WTD_Difference = Data[Current_Tag_Count]-(CALCULATE(SUM(DATA[Current_Tag_Count]),DATEADD(Data[Report_Datetime],-7, DAY)))

Current_Tag_Count_MTD_Difference = Data[Current_Tag_Count]-(CALCULATE(SUM(DATA[Current_Tag_Count]),DATEADD(Data[Report_Datetime],-30, DAY)))

Current_Tag_Count_YTD_Difference = Data[Current_Tag_Count]-(CALCULATE(SUM(DATA[Current_Tag_Count]),DATEADD(Data[Report_Datetime],-365, DAY)))

This seems like it is pulling what I need though I need to do a few more data checks... unless there is a better way to get this.

[Ashish_Mathur]

Hi,

What result are you expecting.  Take a look at the PREVIOUSMONTH() and PREVIOUSYEAR() functions.