Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Aggregating calculated data

I am tring to aggregate data I have in a column by adding the data in the current row to all the previous rows. Sample data below shows the data in the Done column that I would like to aggregate to p...
Need Help
Show and Tell
v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Anonymous

 

Could you share your file so that we could help further on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

Regards,

Cherie

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Anonymous

 

You may create two measures as below:

Monthly Done2 =
CALCULATE (
    COUNT ( Issues[Key] ),
    History[History New Value] = "Done",
    YEAR ( Issues[Updated] ) = 2018,
    Issues[Issue Status] = "1.Done"
)
    + 0

Cumulative Done2 =
SUMX (
    FILTER ( ALL ( Issues ), Issues[Month #] <= MAX ( Issues[Month #] ) ),
    [Monthly Done2]
)

 

Regards,

Cherie

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    v-cherch-msft wrote:

    Hi Anonymous

     

    You may create two measures as below:

    Monthly Done2 =
CALCULATE (
    COUNT ( Issues[Key] ),
    History[History New Value] = "Done",
    YEAR ( Issues[Updated] ) = 2018,
    Issues[Issue Status] = "1.Done"
)
    + 0
    Cumulative Done2 =
SUMX (
    FILTER ( ALL ( Issues ), Issues[Month #] <= MAX ( Issues[Month #] ) ),
    [Monthly Done2]
)

     

    Regards,

    Cherie

    Cherie, this is awesome! Thank you so much. This gives me just what I need for 2018. And it also includes the months where there were no changes, which I need. I imagine it is just a quick edit to change the Monthly Done measure to change it to 2019. 

     

    May I ask for one more suggestion? I will be sharing these reports on a published dashboard and I would like to give people the ability to set the date range so the cumulative count could start at any month, and run to any future month. Is it possible to do this with a slide bar like I used in other dashboards (which you can see in the file I shared with you)?

     

    -Greg