PowerBI - Measure to Summarize Daily data into Monthly Counts

Question

I have a table of defect data that I would like to create a MEASURE that gives me a count of defects for each month. I know I need to use a date table but my attempts thus far haven't worked out.

What I am looking for when this works is a simple count by month:

January 125 February 225 March 220 April 120

Defect Table

Date Table

Here is the Measure I was trying to build without any luck...

Monthly Defects = 

// TOTALYTD(COUNT(Defects[Defect]), 'Date'[Date])

VAR defectDate = FIRSTDATE(Defects[Created Date]) 
VAR defectYear = YEAR(defectDate)
VAR defectMonth = MONTH(defectDate)

RETURN
    CALCULATE (
        COUNT(Defects[Defect]),
        FILTER (
            Defects,
                Defects[Created Date] <= defectDate &&
                (YEAR (Defects[Created Date]) = defectYear) && (MONTH(Defects[Created Date]) = defectMonth)
        )
    )

Here is what I am looking to do in the end.

If you simply want a count per month, then your measure should be Monthly Defects=COUNTROWS(Defects). This is assuming that you have a relationship between your defect table and your date table. Can you explain why your measure needs to be any more complex than that? — dybzon
Your absolutely correct, I was overthinking it completely. Thanks! — xXPhenom22Xx

dybzon dybzon · Accepted Answer · 2017-08-18T16:29:31

If you simply want a count per month, then your measure should be

Monthly Defects=COUNTROWS(Defects)

This is assuming that you have a relationship between your defect table and your date table.

PowerBI - Measure to Summarize Daily data into Monthly Counts

1 Answers