Forum Discussion
Time series aggregation
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?
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.
2 Replies
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.