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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.