Current Month Moving Average

[PrachiD]

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?

[ccakjcrx]

@PrachiD

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])

[PrachiD]

But I want to calculate the Current Month and rest of the value should be blank. 

[ccakjcrx · Accepted solution]

@PrachiD

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!