Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Month on Month - % difference

Hi,    I have a series of facebook posts over a couple of years (by post date), including key stats - reach, impressions, engagement etc. - and am after some help creating month-on-month calculatio...
Need Help
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi, 

 

Wow, the quick measures are great!

 

But unfortunatly it didn't work for me :(

 

The posts are listed like this, with all the variables in the columns... 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Give this a try:

First you will need a date table, there are a few threads around if you don't already have one.

 

Next, if you don't already, create a date field in your Facebook data that aligns all of your dates to the First Date of each month.  This makes Date math later a lot easier.  A simple calculated column like this will work:

WorkingDate = DATE(
    YEAR('YourTable'[DateField]),
    MONTH('YourTable'[DateField]),
    1
)

 

Create a measure that calculates the overall metric you are trying to achieve.  This might be something like

Post Count = CountRows('YourTable')


Next we want a measure that is going to do the Month By Month restricting:

Post Count Delta = CALCULATE(
     [Post Count],
     DATESINPERIOD(
            'DateTable'[Date],
            LASTDATE('DateTable'[Date]),
            -1,
            MONTH
      )
) - CALCULATE(
     [Post Count],
     DATESINPERIOD(
            'DateTable'[Date],
            EDATE(LASTDATE('DateTable'[Date]), 0),  // This is the prior month
            -1,
            MONTH
      )
)

Hopefully this gives you an idea to try.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    Thank you so much for your help!

     

    I've created a date table, but am having trouble relating it to my existing data set. Should I just be able to create a relationship between my two date columns? 

     

    I think there might be duplicates in my post data (eg: multiple posts on the same day/time), is that why I'm having difficulty?

     

    Thanks again for your help!

     

    Sonia

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Dates will only need to be unique within the date table itself.  The relationship should be fine between the two.

       

      I think I know where your issue is.  Your date table is just that, a date table.  Your publshed Date is actually a DateTime.  What you might need to do is create a new calculated column which is the same as your Publised Date, but without the time component.  Try this DAX in a calculated column:

       

      Key Date = DATE(YEAR([Published Date]), MONTH([Published Date]), DAY([Published Date]))

      Now link the Key Date with your date table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thank you so much, Ross! All looks to be linked now! 

         

        Unfortunately, I'm still having a bit of trouble with the month-on-month etc. calcs.  The quick measures don't seem to work for me, so perhaps creating the calculated columns is best? 

         

        I tried your original formula but it didn't seem to work :( 

         

        If, for example, I wanted to a month on month change for some of the below columns, what sort of formula would I use? My table name is called POST DATA.

         

        Also, do you find the KPI card most effective for showing currently month and change % as a visual? 

         

        Thanks again for all of your help, Ross- greatly appreciated! Hopefully one day I'll know as much as you do!