Time series aggregation

[Anonymous]

I have several daily sales datasets which I want to aggregate and sum to monthly sales for each dataset and plot on separate line charts. I don’t want to use date hierarchy because then it would merge the same months of each year. You can use group by in the query editor but it would be inefficient when you have multiple tables. Is there a more efficient way to do this so you can do the aggregation for all tables in one step?

[amitchandak · Accepted solution]

@Anonymous , Not very clear.

You should create a date table. And it should have all the required columns. You can create a custom hierarchy if needed

https://www.tutorialgateway.org/create-hierarchy-in-power-bi/

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Thanks Amit! Your sample PowerBI report (pbix) was very helpful. It was a matter of adding a month-year column in the date table and relating the date table to the sales tables using date as key. It worked. Thanks again!