Need Help Displaying a Rolling Count by Quarter

[emuller]

Hi All,

I have been trying to specifically display the quarter/year in a line chart. I have created a measure that works (sometimes) to show the rolling count of a field over time. When trying to display this as a quarter view I get very odd results.
My closest attempt so far shows the month/year as seen below. In this example, I have the Date not as hierarchy. When converting it to a hierarchy the entire chart forgets the rolling count and just shows me the individual count by month/quarter/year, etc.
Could my measure be the issue? I specifically need COUNT and not SUM.

Measure DAX code:

RunningTotal =

Calculate(COUNT('Table1'[ID]),

        FILTER(ALL('Table1'[Date]),'Table1'[Date] <= MAX('Table1'[Date])))

[v-yanjiang-msft]

Hi @emuller ,

I create a sample and reproduce your problem.

Simply modify the formula to:

RunningTotal =
CALCULATE (
    COUNT ( 'Table1'[ID] ),
    FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MAX ( 'Table1'[Date] ) )
)

Get the correct result:

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[emuller]

Thanks for the response. 
While this works to create a table like you showed, I still cannot create a Line Chart that shows the quarterly intervals. I believe the issue is when I add another field to the legend I somehow cannot show quarters anymore

[v-yanjiang-msft]

Hi @emuller ,

Glad you find the cause, if you need further help, I need the sample data without sensitive data to dig.

Best regards,

Community Support Team_yanjiang

[emuller]

Hi @Ashish_Mathur,
Thank you for your advice.
I just tried this and do not think it is what I need. I still cannot fork down to the quarter level. Also I do not need YTD just a rolling count, regardless of time.

[Ashish_Mathur]

Hi,

Revise the measure to

Running count = calculate([count],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Ensure that the Year and quarter on the X-axis of the visual are dragged from the Calendar Table.

Hope this helps.

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name, Month number and Quarter.  Sort the Month name column by the Month number.  Create a relationship (Many to One and Single) from the Date column of Table1 to the Date column of the Calendar Table.  To your visual, drag Year and quarter from the Calendar Table.  Write these mesures

Count = COUNT('Table1'[ID])

Count YTD = calculate([count],datesytd(calendar[date],"31/12"))

Hope this helps.