Forum Discussion

emuller's avatar
emuller
Frequent Visitor
3 years ago

Need Help Displaying a Rolling Count by Quarter

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

Need Help

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    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.

  • emuller's avatar
    emuller
    Frequent Visitor
    3 years ago

    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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      3 years ago

      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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      emuller
      Frequent Visitor
      3 years ago

      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's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        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