Forum Discussion

clarkbj's avatar
clarkbj
Frequent Visitor
8 years ago
Solved

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...
  • clarkbj's avatar
    clarkbj
    8 years ago

    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.

clarkbj's avatar
clarkbj
Frequent Visitor
8 years ago

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's avatar
Ashish_Mathur
Super User
8 years ago

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's avatar
    clarkbj
    Frequent Visitor
    8 years ago

    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's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

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

      • clarkbj's avatar
        clarkbj
        Frequent Visitor
        8 years ago

        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.