Forum Discussion
Monthly Calculation Table
Hello everyone, i’m a new Power BI user so i apologize for my entry level question.
| Date | Issue |
| 01/02/2023 | A |
| 01/15/2023 | B |
| 02/02/2023 | A |
| 03/27/2023 | C |
| 03/27/2023 | C |
| Month | Issue A | Issue B | Issue C |
| January | 1 | 1 | 0 |
| February | 1 | 0 | 0 |
| March | 0 | 0 | 2 |
| Total | 2 | 1 | 2 |
Hi clim2f88j
In order to achieve your goal, you can:
Preparing data in PQ is the first step:
Add a column with the month names2. 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
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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:
It includes samle file
I hope it helps, Rita
5 Replies
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
Hi clim2f88j
In order to achieve your goal, you can:
Preparing data in PQ is the first step:
Add a column with the month names2. 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
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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:
- 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)
- The month collumn is in alphabetical order. Any way to change that to the correct "yearly order"?
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:
It includes samle file
I hope it helps, Rita
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.
