Forum Discussion
Month on Month - % difference
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.
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...
- Anonymous8 years agoNot applicable
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.
- Anonymous8 years agoNot applicable
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
- Anonymous8 years agoNot applicable
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.