Forum Discussion
Calculated Measure/Column to show difference in value from last 7 days , last month, and last year
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.
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.
7 Replies
- Ashish_Mathur
Super User
Hi,
What result are you expecting. Take a look at the PREVIOUSMONTH() and PREVIOUSYEAR() functions.
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.
