Forum Discussion
Month on Month - % difference
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.
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!
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.
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)
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 :(
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,
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.
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
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
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
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.