cancel
Showing results for
Did you mean:
Regular Visitor

## How to show the count of rows in Powerbi matrix visual based on Quarter wise cumulative way

Hi Experts,
I need some help on this counting rows in my data, here is the sample data which i am using to build a report.

 User Course_Date(mm/dd/yyyy) Quarter Year abc 7/16/2020 Qtr3 2020 def 12/24/2020 Qtr4 2020 ghi 1/1/2021 Qtr1 2021 jkl 4/1/2021 Qtr2 2021 mno 7/9/2021 Qtr3 2021 adg 11/5/2021 Qtr4 2021 gjm 2/4/2022 Qtr1 2022 hhh 2/5/2022 Qtr1 2022 iii 7/7/2022 Qtr3 2022

I want to show the count data in Matrix visual as below.

Simple logic = Count rows till that particular Quarter from starting.

1 ACCEPTED SOLUTION
Super User

Here is one way. First create a date table for the model (recommended best practice). You can use the following DAX code for a new table (my fact table is 'fTable'):

``````Date Table =
CALENDAR ( MIN ( fTable[Course_Date] ), MAX ( fTable[Course_Date] ) ),
"MonthNum", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"QuarterNum", QUARTER ( [Date] ),
"Quarter", "Qtr" & QUARTER ( [Date] ),
"YYYYQ",
YEAR ( [Date] ) * 10
+ QUARTER ( [Date] ),
"Year & Quarter",
YEAR ( [Date] ) & " " & "Qtr"
& QUARTER ( [Date] ),
"Year", YEAR ( [Date] )
)
``````

Sort the Month field by the MonthNum field; sort the Quarter field by the QuarterNum field; sort the Year & Quarter field by the "YYYYQ" field.

Create a one-to-many single relationship between the 'Date Table'Date] and the fTable[Course_date]. The model looks like this:

Now create the measure for the matrix:

``````Quarter Cumul Countrows =
CALCULATE (
COUNTROWS ( fTable ),
FILTER (
ALL ( 'Date Table' ),
'Date Table'[YYYYQ] <= MAX ( 'Date Table'[YYYYQ] )
)
)
``````

Use the 'Date Table'[Year & Quarter] field for the matrix columns and the [Quarter Cumul Countrows] measure as values to get

Sample PBIX file attached

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

3 REPLIES 3
Super User

Here is one way. First create a date table for the model (recommended best practice). You can use the following DAX code for a new table (my fact table is 'fTable'):

``````Date Table =
CALENDAR ( MIN ( fTable[Course_Date] ), MAX ( fTable[Course_Date] ) ),
"MonthNum", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"QuarterNum", QUARTER ( [Date] ),
"Quarter", "Qtr" & QUARTER ( [Date] ),
"YYYYQ",
YEAR ( [Date] ) * 10
+ QUARTER ( [Date] ),
"Year & Quarter",
YEAR ( [Date] ) & " " & "Qtr"
& QUARTER ( [Date] ),
"Year", YEAR ( [Date] )
)
``````

Sort the Month field by the MonthNum field; sort the Quarter field by the QuarterNum field; sort the Year & Quarter field by the "YYYYQ" field.

Create a one-to-many single relationship between the 'Date Table'Date] and the fTable[Course_date]. The model looks like this:

Now create the measure for the matrix:

``````Quarter Cumul Countrows =
CALCULATE (
COUNTROWS ( fTable ),
FILTER (
ALL ( 'Date Table' ),
'Date Table'[YYYYQ] <= MAX ( 'Date Table'[YYYYQ] )
)
)
``````

Use the 'Date Table'[Year & Quarter] field for the matrix columns and the [Quarter Cumul Countrows] measure as values to get

Sample PBIX file attached

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Regular Visitor

Awesome, its working.

Thanks for the swift and easy solution

Regular Visitor

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors