Forum Discussion
Month on Month - % difference
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.
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