Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running total by date

Hi!

 

I am very new to PowerBI and have yet to delve into DAX expressions.

 

I work in clinical trials and am trying to track our enrollment data across each study. I would like to know how many people have signed consent on a given day.

 

Basically I have a column of data like this:

1-Oct
1-Oct
1-Oct
2-Oct
3-Oct
3-Oct

3-Oct

 

And I would like to transform this into a table like this:

 Consents on this DayTotal Consented
1-Oct33
2-Oct14
3-Oct37

 

 

The end goal is to show a clustered column with consents per day by study. This will be overlaid with a line chart showing the cumulative consents. I have successfully created the clustered column chart but am struggling with having a running total. This data set is pulled live, so i need this solution to account for the fact that this is not a static data set and new dates will appear all the time.

 

Thank you for any help! I am sure this is a straight forward solution, I am just so new to PowerBI i am unsure what to even search to find my solution.

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Solved by creating a table of all possible dates:

    Cumulative Consent Table = CALENDAR(FIRSTDATE('Study'[Date Consent Signed]), LASTDATE('Study'[Date Consent Signed]))

    Added a column that counts the number of consents for each day using two separate tables:

    Consent Count = CALCULATE(COUNTA('Study'[Date Consent Signed]),FILTER(ALL('Study'[Date Consent Signed]), 'Study'[Date Consent Signed]='Cumulative Consents'[Date]))

    And finally creating the cumulative consent column:

    Cumulative Consent Count = 
CALCULATE (
    SUM ( 'Cumulative Consents'[Consent Count] ),
    ALL ( 'Cumulative Consents' ),
    'Cumulative Consents'[Date] <= EARLIER ( 'Cumulative Consents'[Date] )
)

3 Replies

  • turkfoster's avatar
    turkfoster
    Frequent Visitor
    6 years ago

    I just did something like this.  Go into the power query editor and sort your date column oldest to newest then add an index column starting with 1. Its a buton in the Add Column Tab and it gives you an option to start at 0 or 1. After you do this add a custom column and call it whatever you want. Put this code in there but change [COUNT] to the name of the colum with the values you're trying to build your running total off of. This should work - It worked for me.

     

    =List.Sum(List.Range(#"Added Index"[COUNT],0,[Index]))

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you. That seems like a clever solution but I get the following error:

       

      Expression.Error: We cannot apply operator + to types Date and Date.
      Details:
      Operator=+
      Left=5/6/2019
      Right=5/6/2019

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Solved by creating a table of all possible dates:

    Cumulative Consent Table = CALENDAR(FIRSTDATE('Study'[Date Consent Signed]), LASTDATE('Study'[Date Consent Signed]))

    Added a column that counts the number of consents for each day using two separate tables:

    Consent Count = CALCULATE(COUNTA('Study'[Date Consent Signed]),FILTER(ALL('Study'[Date Consent Signed]), 'Study'[Date Consent Signed]='Cumulative Consents'[Date]))

    And finally creating the cumulative consent column:

    Cumulative Consent Count = 
CALCULATE (
    SUM ( 'Cumulative Consents'[Consent Count] ),
    ALL ( 'Cumulative Consents' ),
    'Cumulative Consents'[Date] <= EARLIER ( 'Cumulative Consents'[Date] )
)