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
edhans's avatar
edhans
Community Champion
7 years ago

Anonymous- see the attached file. I added a date table to your data, then used the TOTALYTD() measure, which will reset itself every January. I added some fake data into the next year so you could see how that works.

 

This will also work if you show the data by quarter, week, or even day.

 

Total Done YTD = TOTALYTD([Total Done],('Calendar'[Date]))

I always recommend you create your own date table. You can see in the file how I did it in Power Query. You can add a lot more columns if you want (short month name, MMM-YY combination, quarter number, etc.)

 

Link to PBIX file.

Anonymous's avatar
Anonymous
Not applicable
7 years ago
edhans wrote:

Anonymous- see the attached file. I added a date table to your data, then used the TOTALYTD() measure, which will reset itself every January. I added some fake data into the next year so you could see how that works.

 

This will also work if you show the data by quarter, week, or even day.

 

Total Done YTD = TOTALYTD([Total Done],('Calendar'[Date]))

I always recommend you create your own date table. You can see in the file how I did it in Power Query. You can add a lot more columns if you want (short month name, MMM-YY combination, quarter number, etc.)

 

Link to PBIX file.

edhans, I downloaded your file but can't open it. Apparently, I need to install the latest version of Power BI but I don't have access to do that. I am a consultant and they lock this down from me. I am using Version: 2.63.3272.40262 64-bit (October 2018). Would you be able to share it in a way that I can open it with this version?