Forum Discussion
Helper IICalculate weeks between step date and calendar
Hi All,
I have a dataset of all cases that are pending and i need to identify if the wait weeks are increasing or decreasing each month. I need a measure that works out the longest wait per month and show this in a graph so you can see what the longest wait is by month.
Over time data will change as once a case is completed, it will come off the dataset which is fine.
So for example, if i have a case that came in on 14/05/2022, i need to calculate how long this has been waiting for so i would work out number of weeks between 14/05/2022 to 28/02/2025 for latest month but same applies for previous months i.e number of weeks between 14/05/2022 to 31/01/2025, 3/12/2024 etc.
is it possible to acheive this? any workaround also helpful.
thank you in advance
kind regards
Hetal
15 Replies
rajulshahResident Rockstar
Hello Hetal, hpatel24779
Yes, this can surely be possible. But can you please share sample dataset to confirm if it is achievable or not?
hpatel24779Hi,
Below is sample data where i have shown the number of weeks for previous 5 months. i need to be able to calculate this over a period of time.
Step ID Step Date 28/02/2025 31/01/2025 31/12/2024 30/11/2024 31/10/2024 1 06/11/2021 172.9 168.9 164.4 160.0 155.7 2 28/06/2024 35.0 31.0 26.6 22.1 17.9 3 14/05/2022 145.9 141.9 137.4 133.0 128.7 4 12/01/2025 6.7 2.7 0.0 0.0 0.0 5 30/04/2023 95.7 91.7 87.3 82.9 78.6 kind regards
Hetal
- rajulshah
Hi hpatel24779 , I would need a raw sample data with few tables you have. You can upload the sample data files to OneDrive and share the link.
freginierSolution Sage
Hi there!
If I understand correctly, you want to calculate the number of weeks a case has been pending for each month, and track whether wait times are increasing or decreasing over time.
First, create a DAX Measure for Weeks Pending:
Weeks_Pending =
VAR CaseDate = SELECTEDVALUE( Table[CaseDate] ) -- Get the case start date
VAR MaxMonthDate = MAX( Calendar[Date] ) -- Get the last date of the selected month
VAR WeeksBetween = DATEDIFF( CaseDate, MaxMonthDate, WEEK ) -- Calculate weeks differenceRETURN
IF( NOT ISBLANK(CaseDate), WeeksBetween )(This calculates the number of weeks between the case date and the last date of the selected month.)
Use a Line Chart in Power BI:
- X-axis: Month (from your Calendar table
- Y-axis: Weeks_Pending
- Use MAX(Weeks_Pending) to display the longest wait time for each month.
Hope this helps!
😁😁
- hpatel24779
Hi freginier
i have tried this and whilst it does calculate the weeks pending, when plotting into a line graph, i put the month in X-axis and weeks pending in y-axis but no results show.
also, i am not entirely sure what you meant by 'Use MAX(weeks_pending)'
kind regards
Hetal
v-karpurapudCommunity Support
Hi hpatel24779
Could you please confirm if your query have been resolved the solution provided by rajulshah ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.
Thank you
- v-karpurapud
Hi hpatel24779
We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.
If our response was helpful, please mark it as the accepted solution and give a kudos, as this helps other members in community.
Thank you
- v-karpurapud
Hi hpatel24779
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.