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

[Imranimzzy]

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.

Thanks in advance

[PaulDBrown · Accepted solution]

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] )
)

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

[Imranimzzy]

Awesome, its working. 

Thanks for the swift and easy solution

[Imranimzzy]

@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.