Forum Discussion

lwbenso's avatar
lwbenso
Regular Visitor
8 years ago
Solved

Complicated running total- requesting DAX help

Basically I've made a running total chart of a count of organizations (column: "AccountName") who have signed an agreement with my company (column: "DateAgreementSigned) - see screenshot at the botto...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi lwbenso

     

    You could create a measure that calculates RunningTotal = Cumulative Signed - Cumulative Exited

     

    One way to do this would be:

     

    1. Add an inactive relationship between 'accounts'[DateAccountExited] and 'Date'[Date]
    2. Create a measure like this:
      RunningTotal = 
CALCULATE (
    VAR AccountsSigned =
        COUNTROWS ( accounts )
    VAR AccountsExited =
        CALCULATE (
            COUNTROWS ( accounts ),
            USERELATIONSHIP ( accounts[DateAccountExited], 'Date'[Date] )
        )
    RETURN
        AccountsSigned - AccountsExited,
    DATESBETWEEN ( 'Date'[Date], BLANK (), MAX ( 'Date'[Date] ) )
)

    Notes on the measure:

    • I used variables for readability - not required
    • I used DATESBETWEEN ( 'Date'[Date], BLANK (), MAX ( 'Date'[Date] ) ) as an alternative way of generating all dates up to the max date.

    Regards,

    Owen :)

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Try this:

 

RunningTotal = 
CALCULATE(
     COUNTROWS('accounts'),
     'accounts'[DateAccountExited] = BLANK(),     
     FILTER (
         ALL ( 'Date'[Date] ),
         'Date'[Date] <= MAX ('Date'[Date]) 
     )
)

Now you're telling Power BI to only look at rows that do NOT have an Exit date.  If there's an exit date, it will be removed from the calculation.

 

COUNTROWS() is typically faster than COUNTA().

  • lwbenso's avatar
    lwbenso
    Regular Visitor
    8 years ago

    Anonymousthat is closer to what I am looking for, but would it be possible to leave them in the count until the exit date is reached (rather than take them out all together)?

     

    Also- thanks for the tip on using COUNTROWS() :smileyhappy:

    • lwbenso's avatar
      lwbenso
      Regular Visitor
      8 years ago

      Bump...maybe someone else has a tip?

      • OwenAuger's avatar
        OwenAuger
        Icon for Super User rankSuper User
        8 years ago

        Hi lwbenso

         

        You could create a measure that calculates RunningTotal = Cumulative Signed - Cumulative Exited

         

        One way to do this would be:

         

        1. Add an inactive relationship between 'accounts'[DateAccountExited] and 'Date'[Date]
        2. Create a measure like this:
          RunningTotal = 
CALCULATE (
    VAR AccountsSigned =
        COUNTROWS ( accounts )
    VAR AccountsExited =
        CALCULATE (
            COUNTROWS ( accounts ),
            USERELATIONSHIP ( accounts[DateAccountExited], 'Date'[Date] )
        )
    RETURN
        AccountsSigned - AccountsExited,
    DATESBETWEEN ( 'Date'[Date], BLANK (), MAX ( 'Date'[Date] ) )
)

        Notes on the measure:

        • I used variables for readability - not required
        • I used DATESBETWEEN ( 'Date'[Date], BLANK (), MAX ( 'Date'[Date] ) ) as an alternative way of generating all dates up to the max date.

        Regards,

        Owen :)