Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
clarkbj
Frequent Visitor

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. 

 

 

 Capture.JPG 

 

1 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.

View solution in original post

7 REPLIES 7
pxg08680
Resolver III
Resolver III

@clarkbj

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.

 

 

Capture4.JPG

 

 

 

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.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.