cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Imranimzzy
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.

UserCourse_Date(mm/dd/yyyy)QuarterYear
abc7/16/2020Qtr32020
def12/24/2020Qtr42020
ghi1/1/2021Qtr12021
jkl4/1/2021Qtr22021
mno7/9/2021Qtr32021
adg11/5/2021Qtr42021
gjm2/4/2022Qtr12022
hhh2/5/2022Qtr12022
iii7/7/2022Qtr32022

 

 

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

 

Imranimzzy_0-1669485622049.png



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

Thanks in advance

 

1 ACCEPTED SOLUTION
PaulDBrown
Super User
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 =
ADDCOLUMNS (
    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] )
)

 

date table.jpg

 

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:

model.jpgNow 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

result.jpg

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Super User
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 =
ADDCOLUMNS (
    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] )
)

 

date table.jpg

 

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:

model.jpgNow 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

result.jpg

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Awesome, its working. 

Thanks for the swift and easy solution

Imranimzzy
Regular Visitor

@amitchandak  could you please help me? I follow your answers in most of the queries i have seen in community.

Your solutions are so simple.

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.