Forum Discussion
Week Number Time Resovlved Cumulative Database Entrie Dashboard Display
So I am a new Power BI user and I am setting up a dashboard for a Sharepoint List database. All entries are data/time stampled. I wish to display the cumulative entry totals per annual week number (1-52). For example, the first week of January might have 4 entries throughout the week, so on x axis would be 1 and y axis total is 4. For second week of January, there are 4 new entries, so the x axis would be 2 and the y axis would be 8, not 4. Hope that is clear. My problem is visualizing the cumulative entry numbers as well as correlating a data/time stampl with a week number. Any help is greatly appreciated.
2 Replies
- amitchandak
Super User
lswanson1 , You use a formula like this with date calendar. In the calendar you can have a week, month , qtr etc.
What you choose (Week), this will be cumulative of that. Use week on the axis/rows
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))Calendar
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
Week in calendar
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482To 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.So I was able to get the plot for weeks per year number on the x axis to work with a calendar table. I am still working on the cumulative sum portion. My attempt is this:
Measure = TOTALYTD(COUNTROWS('Table Name'), 'Calendar', Calendar')
So as you can see, I have no clue how to set the start and end dates. What I want is to have the start date be the start of the current calendar year and the end date be either today or the end of this year if future dates can be set.
