Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
Can you give some example to calculate difference using the data in your image..
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.
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.
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.
Hi,
Share the link from where i can download your file. Also, show the correct result there.
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.
Hi,
What result are you expecting. Take a look at the PREVIOUSMONTH() and PREVIOUSYEAR() functions.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |