Sum data by month

[mike87]

Hi,

I'm fairly new to Power BI, so this might be an easy fix, but I haven't been able to "google" the answer.

I have a data set with reported incidents which is either resolved within the time limit or not. Unfortunately, I am not able to upload the data due to privacy. However, I have created a very simple example of the data in excel which should illustrate the problem.

I want to crate a new table and group/sum all cases which is resolved within the time limit by month, and calculate the percentage. 

I have literally no clue how to do it with DAX.

Any help would be appreciated.

[mede · Accepted solution]

@mike87 this is fairly easy and there are multiple ways:

For both ways make sure that your Date column is in date format. PBI recognizes your Date as a hierarchy of Year, Quarter, Month and Date; in a visual you can remove year, quarter and date leaving just Month. Alternatively you can create a calculated column for month by New Column >> Month = MONTH('Dim Date'[Date]) 

Easy way: Create a matrix, bring your date (Month) into rows, ResolvedWithinLimit into Columns, incident id (aggregation: count distinct) into values. Then display t the values (count distinct of incident ids) as a percentage of row total using quick measures.

A little bit more advanced way: create a new measures using the magical CALCULATE & ALL DAX functions.

Count of ResolvedWithinLimit = CALCULATE(DISTINCTCOUNT(incident_id), ResolvedWithinLimit = 1)

Count of all resolved = CALCULATE(DISTINCTCOUNT(incident_id), ALL(ResolvedWithinLimit)

ResolvedWithinLimit% = Count of ResolvedWithinLimit / Count of all resolved (change type to percentage)

Create a matrix where Month is in rows and drag these new measures into Values of your matrix.

[Greg_Deckler]

Create a column:

Month = MONTH([Date])

Create a table visualization and put Month and ResolvedWithinLimit (sum). You can also do a "Show As" "Percent of grand total".