Forum Discussion

some_analyst's avatar
some_analyst
Icon for Helper I rankHelper I
1 year ago
Solved

Cumulative sum / deduction descending

Hello, a newbie here and I need some help creating the following graph:

- I have a 260 TicketIDs in my data.

- From colleagues I get an delivery date for each ticket, which looks like that: Ticket 1: Due by 15/5/25

 

I want to create a graph where on X axis I will have a date of 10 march 2025, and end date of 1 june 2025. Data comes from dim_date: PKDate

Ploted as a line will be cumulative sum of still open tickets, descending. so graph will start from 260 and will deduct how many are due on a date.

 

Can someone help how to do that please?

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

     

    expected result measure: = 
CALCULATE (
    COUNTROWS ( VALUES ( data[ticket_id] ) ),
    FILTER ( ALL ( 'calendar' ), 'calendar'[Date] >= MAX ( 'calendar'[Date] ) )
)

     

4 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    1 year ago

    Hi some_analyst,

    Make sure that your date (PKDate) is a continuos date field.

    Use below dax to achieve it 

     

    Open Tickets =
    VAR SelectedDate = MAX('dim_date'[PKDate])
    RETURN
    CALCULATE(
    COUNT(Tickets[TicketID]),
    Tickets[Due Date] >= SelectedDate
    )

     

    Add pkdate to x-Axis, add Open tickets measure as a line chart on Y-Axis.

    This will show a descending cumulative count of open tickets over time.

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

    • some_analyst's avatar
      some_analyst
      Icon for Helper I rankHelper I
      1 year ago

      thanks for that grazitti_sapna , I can't seem to get this to work: 
      I have this measure below, but it gives me not the cummulative but progressive counts: 

      Cummulative expected =
          var SelectedDate = max(Date_DIM[Date])
          return
          CALCULATE(
              DISTINCTCOUNT(StaticBacklog[ID_Number_Desc]),
              StaticBacklog[Date Due for Decision] >= SelectedDate)
       

       

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

     

    expected result measure: = 
CALCULATE (
    COUNTROWS ( VALUES ( data[ticket_id] ) ),
    FILTER ( ALL ( 'calendar' ), 'calendar'[Date] >= MAX ( 'calendar'[Date] ) )
)

     

  • Vijay_Chethan's avatar
    Vijay_Chethan
    Icon for Resolver III rankResolver III
    1 year ago

    I dont think you need to cumilative but you could create a measure to calculate the count of tickets that are open and in range minimum date in ALL(your table) to max date in (your table)[This gives you the current date which is effecting your data in graph x axis 
    And when it comes to decending graph if your tickets are being closed for later dates it will automatically lead to decending graph


    if this helps please mark as solution