cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Community Champion

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
Community Champion

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors