Monthly Calculation Table

[clim2f88j]

Hello everyone, i’m a new Power BI user so i apologize for my entry level question.

I need to create a table that gives me the separated total amount of “issues” per month and totals. This data is being pulled from a SharePoint connection that is updated daily.

The columns i have are “Date” and Issues”.

The “Date” and “Issue" columns are formatted as such and contain this:

Date	Issue
01/02/2023	A
01/15/2023	B
02/02/2023	A
03/27/2023	C
03/27/2023	C

I need to create a table that would calculate the above for me and look like this:

Month	Issue A	Issue B	Issue C
January	1	1	0
February	1	0	0
March	0	0	2
Total	2	1	2

Thank you in advance for your help!

[Ritaf1983 · Accepted solution]

Hi @clim2f88j 

In order to achieve your goal, you can:

Preparing data in PQ is the first step:
Add a column with the month names

2. Add column with concatenating string "issue" to your issues:

after it press "close and apply"
3 . Create a measure to count rows for issues with Dax: 

ISS_QTY = count('Table'[ Issue ])

4. Create Matrix with , months in rows, issues on columns and your measure on values

Link to sample file 

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

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort Month name by Month number.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag Year and Month name from the Calendar Table.  Write this measure

Measure = coalesce(countrows(Data),0)

Hope this helps.

[Ritaf1983 · Accepted solution]

Hi @clim2f88j 

In order to achieve your goal, you can:

Preparing data in PQ is the first step:
Add a column with the month names

2. Add column with concatenating string "issue" to your issues:

after it press "close and apply"
3 . Create a measure to count rows for issues with Dax: 

ISS_QTY = count('Table'[ Issue ])

4. Create Matrix with , months in rows, issues on columns and your measure on values

Link to sample file 

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

[clim2f88j]

Thank you, this worked...for the most part.

I didn't do step 2 because it was not needed to concatenate the "issues".

Two issues i'm having:

1. By doing this, it includes ALL the months/years. How can i select in the table to only show the totals for 2023? (i know i didn't state this in my original post)
2. The month collumn is in alphabetical order. Any way to change that to the correct "yearly order"?

[Ritaf1983 · Accepted solution]

Hi @clim2f88j 

1 use filter / slicer by year and just select the year you interested to analyse 

2. You need to sort months names by column of their number .

Please refer my answer in the linked discussion:

https://community.powerbi.com/t5/Desktop/Displaying-All-Months-on-a-Continuous-Line-Chart/td-p/32411...

It includes samle file

I hope it helps, Rita

[Thejeswar]

Hi @clim2f88j ,

I am giving below the steps to achieve this from Power Query editor

1. Set your Date column with Date Data Type. If your column creates an issue, split the column into Month, Date and Year and create a new date column using the below functions

#date([Year],[Month],[Day])

2. Create a new column by concatenating Issue with A/B/C. You end result will be Issue A, Issue B, Issue C

Text.Combine({" Issue", [#" Issue "]}), type text)

3. Create a column for Month Name from the Parse option in Add Column

4. Create a measure as below

No of Issues = COUNT('Table (2)'[ Issue ])

4. Drag the New Month Name Column into the Pivot row, Issue Name to the Pivot Column and No of Issues to Value

5. Sort the Month Name using Month Number by using the "Sort by Column" option

The Output will be as shown below