Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
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 =
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
Community Champion
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 =
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.