Forum Discussion
Calculated Table for Month over Month Changes
Hi xzfujc
The best way is to think of measures instead of tables/columns. You can then calculate the number of gates for the given month, the number of gates for the previous month, and then the difference.
You will need a date column to key off of, but since you have Month and Year, that's easy enough to create.
//Calculated Column Dt = DATE(Year, Month, 1) //Measures Total Gates = CALCULATE(SUM(Count)) LM Total Gates = CALCULATE([Total Gates], PREVIOUSMONTH(Dt)) Variance = [Total Gates] - [LM Total Gates]
This will allow you to create a table visual, and if you want to look at it by just Gate instead of by Gate and Emp ID, the calculation will take care of itself.
Hope this helps
David
Hi xzfujc
The best way is to think of measures instead of tables/columns. You can then calculate the number of gates for the given month, the number of gates for the previous month, and then the difference.
You will need a date column to key off of, but since you have Month and Year, that's easy enough to create.
//Calculated Column Dt = DATE(Year, Month, 1) //Measures Total Gates = CALCULATE(SUM(Count)) LM Total Gates = CALCULATE([Total Gates], PREVIOUSMONTH(Dt)) Variance = [Total Gates] - [LM Total Gates]
This will allow you to create a table visual, and if you want to look at it by just Gate instead of by Gate and Emp ID, the calculation will take care of itself.
Hope this helps
David
I think your solution will accomplish what I need.
I am running into an issue with
LM Total Gates = CALCULATE([Total Gates], PREVIOUSMONTH(Dt))
It returns a 0. So my total variance is now equal to my total gates. Working on resolving. Is there a way to say something like the following?
Total Gates = CALCULATE(SUM(Count), CurrentMonth(Dt))
Change the variance calculation to
Variance = IF(ISBLANK([LM Total Gates]), 0, [Total Gates] - [LM Total Gates])
(or use BLANK() instead of 0 if you want to show no value in Variance for the first month)
Hope this helps
David
Really appreciate all the help, and I do believe the solution you provided is the solution I need. I am having trouble fully implementing it though, for some reason I can not get the LM Gates Total to sum anything which gives me a variance of 0.
Is there any reason you could think of why the the LM Total Gates returns null?
Hi xzfujc
You may drag the 'Dt' column to the table visual instead of 'Month' column.
LM Total Gates = CALCULATE([Total Gates], PREVIOUSMONTH(Dt))
Regards,
Cherie
