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

     

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)