Month on Month - % difference

[Anonymous]

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

I'm including some trend graphs (with data from 2016-present) , but also some card visuals with the just the latest month (eg: volume of published posts in Jan) and would like for these to show Month-on-Month variance %. 

Anyone able to help?

Many thanks! 

Sonia

[Anonymous]

Hi Sonia,

Did you try the quick measure Month-over-month change?

[Anonymous]

Hi, 

I'm not sure, what is the quick month on month measure ?

[Anonymous]

If you right click on your column name, you will see an option to create a New Quick Measure. Refer to the screenshot below.

You may have to mark your date table as Custom Date table with no duplicate values.

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2018-feature-summary/#customDateT...

 

[Anonymous]

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]

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]

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]

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]

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! 

[Anonymous]

I mentioned this in my original reply but that was friday so i didn't think of it when i did my key date.  Try changing that to this formula:

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

This will align your data to the month, rather than the day.

[Anonymous]

I should also mention for completeness, the quick formula i wrote in my original reply is a Delta formula.  To get a percentage just use the DIVIDE function instead which will look something like = DIVIDE(<First Calc Statement>, <Second Calc Statement)

[Anonymous]

Thank you, Ross 

I'm still having trouble with the 'Calculate' function, I'm unable to replicate your example. It's prompting me for an 'Expression', I can't seem to add the Post Count column in... 

Sorry for all the questions 😞

[Anonymous]

Hi Ross,

I think I'm getting a little closer... I tried the below formula instead to get previous month's figures but it's not returning any results... 

 any ideas?

Thanks again, 

[Anonymous]

Perhaps i didn't explain well.  [Post Count] wasn't supposed to be a field but an example of a measure.  What you need to do is a create a measure that performs your KPI, knowing that it doesn't handle the correct time constraint.  The calculate statement will run your measure and force in your applicable dates.

You will notice in my example I created the measure Post Count, which does a count of the number of rows in the data set.  The expectation here, for my example, was that each post would be a single row in the data.  Thus if I was to count my pretend data, i could get a count of all the posts in a given time period.

[Anonymous]

Hi @Anonymous

Ok, I think I understand a little better now

That being said, I'm still having trouble getting a formula to work... 

I've created a column for 'average engagement rate' (=AVERAGE('POST DATA[ENGAGEMENT RATE]) ) and then have tried to apply the calculate statement to this but it still doesn't recognise it as a Measure. 

Ideally, I'm wanting the average engagement rate for the current month vs the previous 

Thanks again, and sorry for all the confusion

Sonia

[Anonymous]

I noticed you said that you created a column for your Average.  That might be the problem.  A Measure and a Column are not the same things, although their buttons are next to each other. Try recreating that formula as a measure rather than a column

[Anonymous]

Oh, great! yeah, i was usually a new column instead. I've now created the Average engagement as a Measure and was able to add it into the formula, but now I'm getting a 'circular dependency' error 😞

Am I using the right comulm from my Date table (DateKey)?

Thanks again for all of your help

[Anonymous]

From afar this will be harder to diagnose, but you'll need to have a look at the 2 columns mentioned in the error.  Its likely this formula isn't the problem but is highlighting another issue.