Current Month Moving Average

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!