Forum Discussion
Current Month Moving Average
Hello,
I want to Calculate Current month value.
Suppose in my case the current month is 17-Mar. And I should be able to show the the average for the Month 17-Mar.
In my case I m not getting the value 6.5.
I m getting a constant value through out the table.
As it is a Moving average iwant direct value (ie.,6.5) so is there any way or Measure where we can get the direct value for the month 17-Mar?
Hey!
I believe I have something that may work for you. You can access my .pbix file HERE
In order to verify it only showed data on the table, under the 'Average C/D' column, I had to expand the date values in the data set so that the current month would be represented.
Here is a screenshot of the outcome I have arrived at:
I wanted to use an if statement to compare the listed date with the current date, and then divide 'sum count' by 'month no' if TRUE, ELSE ' ' (i.e., blank). However, I kept getting the following error, "Expressions that yield variant data-type cannot be used to define calculated columns." I believe this has something to do with the fact that I was either going to return a number or a string.
The only way I could get around this was to add columns that:
1. Compare listed month to current month
2. Divide 'sum count' by 'month no'
3. Looked for True in step 1, and return value in step 2 if True, and " " if False
Average C/D = IF ( CONCATENATE ( MONTH ( Sheet1[Month Year] ), YEAR ( Sheet1[Month Year] ) ) = CONCATENATE ( MONTH ( TODAY () ), YEAR ( TODAY () ) ), Sheet1[Average (C/D) Hidden], "" )Another caveat here is you have to change the data type for the value in step 2's column to string; otherwise, you are presented with the same error concerning variant data types.
Hope this helps!
3 Replies
Hello!
I can get this in a calculated column, but not a measure.
Here is my final table showing the value it seems you are looking for:
After getting your data into a table, you add the average column as the calculated column, and use the following expression:
Average Column = DIVIDE(Sheet1[Sum Count],Sheet1[Month No])
But I want to calculate the Current Month and rest of the value should be blank.
Hey!
I believe I have something that may work for you. You can access my .pbix file HERE
In order to verify it only showed data on the table, under the 'Average C/D' column, I had to expand the date values in the data set so that the current month would be represented.
Here is a screenshot of the outcome I have arrived at:
I wanted to use an if statement to compare the listed date with the current date, and then divide 'sum count' by 'month no' if TRUE, ELSE ' ' (i.e., blank). However, I kept getting the following error, "Expressions that yield variant data-type cannot be used to define calculated columns." I believe this has something to do with the fact that I was either going to return a number or a string.
The only way I could get around this was to add columns that:
1. Compare listed month to current month
2. Divide 'sum count' by 'month no'
3. Looked for True in step 1, and return value in step 2 if True, and " " if False
Average C/D = IF ( CONCATENATE ( MONTH ( Sheet1[Month Year] ), YEAR ( Sheet1[Month Year] ) ) = CONCATENATE ( MONTH ( TODAY () ), YEAR ( TODAY () ) ), Sheet1[Average (C/D) Hidden], "" )Another caveat here is you have to change the data type for the value in step 2's column to string; otherwise, you are presented with the same error concerning variant data types.
Hope this helps!