Count Values from one table using based on month and year and add to another table (Pbix included)

[PowerBiNoob37]

Hi,

I have a bit of incident data coming from a sharepoint list, every row contains a date of incident and incident type which is a choice field.

Here is a sample of my data

So i need to count the amount of incidents every month and display them in a datamatrix like the following excel image below, i understand i can use a datamatrix and use the totals however i dont want that i need to create a new table that counts these values and displays them in the following way for futher analysis.

Here is my pbix file

https://1drv.ms/u/s!As_sc_x3g6_qkGn9-_Z88kpGqr3P

Any help would be appreciated.

[v-henryk-mstf]

Hi @PowerBiNoob37 ,

Whether the advice given by @HotChilli  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[HotChilli · Accepted solution]

Power Query:

Add a column for 1st day of the month (can do this from the interface->Select date column, go to Add Column-> From Date and Time on the ribbon)

Remove the original date column.

Group By StartOfMonth and Incident using Count Rows as aggregation.

This gives a 3 column table which is pretty good for storage and analysis.

If you want a table as shown, then Pivot the IncidentType column

[PowerBiNoob37]

---

@HotChilli wrote:

Power Query:

Add a column for 1st day of the month (can do this from the interface->Select date column, go to Add Column-> From Date and Time on the ribbon)

Remove the original date column.

Group By StartOfMonth and Incident using Count Rows as aggregation.

This gives a 3 column table which is pretty good for storage and analysis.

If you want a table as shown, then Pivot the IncidentType column

---

Thanks,

I didnt check my emails all day and so i didnt see your response and only came back here to post my answer, ill give you the solutions, because your right that would have been correct. But heres what i did to get the same results

I created a simple distinct measure in DAX

TotalInjuries = DISTINCTCOUNT(InjuriesTable[Injury Type])

and then created a table referencing the measure and using several if statments to group it by, it then created a table like i wanted and referenced the date as i set up a one to many relationship.

IncidentTotalTable = ADDCOLUMNS(IncidentTable, "Date", DATEVALUE(IncidentTable[InjuryDate]), "FAI" , IF( [Injury Type] = "FAI", [TotalInjuries] ), "MTI" , IF([Injury Type] = "MTI",  [TotalInjuries]), "LTI" , IF([Injury Type] = "LTI", [TotalInjuries]))

 I like your way better, except i would have had to create another table.