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] ),
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 =
COUNTROWS ( fTable ),
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
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.