Forum Discussion
Week to Week Trend Analysis
Not sure what is the issue.
In you date table add, these date. This might be need in open formula. Also you need to adjust first and last week in calendar
Week Start date = DATEADD('Compare Date'[Compare Date],-1*WEEKDAY('Compare Date'[Compare Date])+1,DAY)
Week End date = DATEADD('Compare Date'[Compare Date],7-1*WEEKDAY('Compare Date'[Compare Date]),DAY)
Now to get open, close you can refer.
Ideally min max date should work, when you view by time. But if needed, use week start and end date.
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
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
amitchandak thank you for your reply.
I've adjusted my dates table so it has Week Start / Week End.
I can see how the HR example you sent could potentially work, however I see two issues with the data I have. Let me know what you think.
1. I dont have "start" and "end" dates. I have a "Scheduled Date" for inspections and then a Status. Specfically I am trying to see each week what is going on with "Failed" inspections.
For Example
- i.e - Week 1/6/2020 to 1/11/2020 there were a total of 8 failed inspections in the system.
- i.e - Then on week 1/13/2020 to 1/18/2020 there were a total of 2 failed in the system.
So my trend from 1/6/2020 to 1/13/2020 would be a line graph going from 8 to 2.
2. How do I perform a Count function on text? In the HR example they were able to count employee ID, but how would I go about Counting Failed status's by week?
Since my data is constantly changing each week, how would I even compare this week to previous weeks?
I was checking the data. I per you there 8 insepection failed in week on 1/6/2020. I did not get that via status. Can you let me know calculation.