Measure in a calculated column per month

[Anonymous]

I created a measure that calculates an average from answers on a survey. Each survey was submitted on a different date and they are all stored in the Survey table. This measure calculates average of answers from particular questions from each type of survey.

Now, I want to create a new table with the following columns: 

-Month (Jan, Feb, Mar etc)

-AverageCurrentMonth -average of answers from the measure for coresponding month

-AverageLastMonth - average of answers from the measure per last month (so that I can compare this in my KPI table)

This is the measure I am trying to use in the calculated column. It's made up of smaller measures.

Project Management = VAR Count_ =

IF ( [Project M ESC Q1] <> BLANK (), 1, 0 )

+ IF ( [Project M FSR Q1] <> BLANK (), 1, 0 )

+ IF ( [Project M GC Q1] <> BLANK (), 1, 0 )

+ IF ( [Project M GC Q2] <> BLANK (), 1, 0 ) + IF ( [Project M GC Q3] <> BLANK (), 1, 0 ) + IF ( [Project M GS Q1] <> BLANK (), 1, 0 ) + IF ( [Project M RSO Q1] <> BLANK (), 1, 0 ) + IF ( [Project M RSO Q2] <> BLANK (), 1, 0 ) RETURN

IF(DIVIDE ( [Project M ESC Q1]+[Project M FSR Q1]+[Project M GC Q1]+[Project M GC Q2]+[Project M GC Q3]+[Project M GS Q1]+[Project M RSO Q1]+[Project M RSO Q2], Count_ )=0,"N/A",DIVIDE ( [Project M ESC Q1]+[Project M FSR Q1]+[Project M GC Q1]+[Project M GC Q2]+[Project M GC Q3]+[Project M GS Q1]+[Project M RSO Q1]+[Project M RSO Q2], Count_ ))

[amitchandak]

@Anonymous ,Can you share sample data and sample output.

[Anonymous]

---

@amitchandak wrote:

@Anonymous,Can you share sample data and sample output.

---

[v-yuta-msft]

@Anonymous ,

This error message means that the datatype should be explicit in the calculate column. You should not define uncertain datatype, change "N/A" with BLANK() and try again.

Project Management =
VAR Count_ =
    IF ( [Project M ESC Q1] <> BLANK (), 1, 0 )
        + IF ( [Project M FSR Q1] <> BLANK (), 1, 0 )
        + IF ( [Project M GC Q1] <> BLANK (), 1, 0 )
        + IF ( [Project M GC Q2] <> BLANK (), 1, 0 )
        + IF ( [Project M GC Q3] <> BLANK (), 1, 0 )
        + IF ( [Project M GS Q1] <> BLANK (), 1, 0 )
        + IF ( [Project M RSO Q1] <> BLANK (), 1, 0 )
        + IF ( [Project M RSO Q2] <> BLANK (), 1, 0 )
RETURN
    IF (
        DIVIDE (
            [Project M ESC Q1] + [Project M FSR Q1] + [Project M GC Q1] + [Project M GC Q2] + [Project M GC Q3] + [Project M GS Q1] + [Project M RSO Q1] + [Project M RSO Q2],
            Count_
        ) = 0,
        BLANK (),
        DIVIDE (
            [Project M ESC Q1] + [Project M FSR Q1] + [Project M GC Q1] + [Project M GC Q2] + [Project M GC Q3] + [Project M GS Q1] + [Project M RSO Q1] + [Project M RSO Q2],
            Count_
        )
    )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Ok, I created another measure with Blank() instead of "N/A" and named it '2 Project Management'. I get another error:

[Anonymous]

I found a solution to my own problem:

Last Month Project Managment = CALCULATE([Project Management] , DATEADD('Date'[Date] , -1 , MONTH))

Now, how do I get a rolling average for this measure? And a rolling average for same measure ending one month earlier? When I click on a month on the slicer I want to see rolling average up until the month I clicked on, not average per that month.

[v-yuta-msft]

@Anonymous ,

What does "Rolling average" mean? Could you show the logic using some expression?

Regards,

Jimmy Tao

[Anonymous]

I forgot to mention that the measure for 'Project Management' works exactly as intended when I add a slicer for years and quarters. So for any other graph I can slice by quarter - the measure will calculate the average of answers from that quartrer. I have six of those measures: Project Management, Personnel Management, Communication, etc. I want to show KPIs for each category. I'd like to use the KPI matrix, but I can't come up with how to get the value from last month/quarter for comparison. The matrix takes the result of the last survey if I use a date field, or month or quarter if I use those columns. However the sparkline only shows averages for particular months, not cumulative. Also I can't figure out how to compare rolling average for this month/quarter to rolling average from last month/quarter.

[Anonymous]